[LibreOffice] Exclude cells from being counted in a formula due to colour


#1

I want to do the opposite of this.
I want a formula to conditionally exclude cells from being taken into the calculation, when the cells are marked green, yellow or red.


#2

This is a find and replace thing - not a formula thing.
So you are saying

a=1
b=2 style red
c=3 style blue

sumIF(A1:C1) != style

You are basically saying that, right?


#3

This seems like what I was searching for, but I didn’t know that it actually recognizes the word “style” within a formula. I’ll try it out and update this.

So far after reading more documentation on this, I found the following formula:

=SUMIF( C2:C999,"!=style" )

This one doesn’t give an error anymore and the syntax is correct, as well, I think. The issue is now that it actually doesn’t calculate anything, because the result remains simply 0 right now.


#4

I’ve never known how to do this in either Calc or Excel, so I’m not sure it’s possible in a simple way. I would probably go down a different route: -

  1. In another set of cells parallel to the ones you’re shading, put a formula which basically replicates the basis on which you are shading the cells (or a value corresponding to the condition of shading the cells if that isn’t driven by conditions that can be tested in a formula).

  2. Use conditional formatting to accomplish the shading in the original cells.

  3. In your SUMIF example, test the value of the formulae/data set up in 1 rather than the shading in the original cells.

A bit clumsy I know, but it should work.


#5

The way I wrote it will not work - that was for reading purposes only.

You might read this link https://forum.openoffice.org/en/forum/viewtopic.php?f=9&p=43334
from OpenOffice forum.


#6

I will look into it.


#7

Im working on a update to a large sports spreadsheet for a local club. I dont like using macros so I have some hidden columns for tracking and applying colors. What you are wanting to do isnt to different than what I need to do so Ill work on it and post what I find.