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.
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?
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.
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: -
-
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).
-
Use conditional formatting to accomplish the shading in the original cells.
-
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.
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.
I will look into it.
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.
2 Likes