Guys, I cannot think in a way to do this.
I have a table with cells filled with green and red backgrounds, depending on other conditionals.
How can I make a formula where it counts +1 if a cell is green, and ignores it if background is red? These cells already have values on them, but not what I need to sum up.
Thanks in advance for any help.
Create the formula using the same conditionals.
.
Is it possible to post an example file as it is formatted?
.
Quoting @Linnix: Guys, I cannot think in a way to do this.
So can’t I. … Except…
You should have made clear if the colors “depending on other conditionals” are created by Conditional Formatting (CF) or by user intervention.
Case 1: Attributes based on CF never are assigned to the respective cells. They are overlaid only, and for that purpose the conditions are only calculated for cells inside the current view. To base calculations by cell formulas on the applcable conditions, you need to do so explicitly, best in a dedicated helper column, and refer the results by your further calculations. Doing it in subexpressions of the final formuas would complicate these, and likely also decrease efficiency.
Case 2: That’s truely bad. A spreadsheet NOT is a scratch-paper. If made for maintaining data collections it needs conscious design. You coded real data by colors? Immediately fix this grave mistake in design by inserting a column for actual content representing the information you associated the colors with. You then can base CF and cell calculations both on the contents of that column.
If you have very many cells whose CellBackColor (or similar) is coding for information this may be time consuming. You then may want to support the process by a user function capable of reading attributes from cells. Ask for further advice then if needed. (How can I get values as cell contents based on cell attributes?) And never make a permanent thing of applying such introspective functions.
===Edit1 2019-05-29 22.25 UTC+02.00===
Just in case you need it:
Function cellBy_SRC_BackRGB(pZ As Long, pY As Long, pX As Long, Optional pDummy) As String
'pDummy shall only provide a way to trigger execution depending on any reference or volatile function.
Dim bckColor As Long, h As String
cellBy_SRC_BackRGB = "fail"
On Error GoTo errorExit
bckColor = ThisComponent.Sheets(pZ-1).getCellByPosition(pX-1, pY-1).CellBackColor
h = "(R,G,B) = (" & Red(bckColor) & "," & Green(bckColor) & "," & Blue(bckColor) & ")"
cellBy_SRC_BackRGB = h
errorExit:
End FunctionOh, I should clarify some things.
It’s not any critical data for companies or anything, just a stupid small sheet for controlling a game details. The “other conditionals” was misleading indeed, I fill up manually the values.
I also tried to sum +1 if the cells have a >0 value in it (case for all green cells), but I was also unable to find a function for that.
Well, data are data, and I still don’t know for sure whether the colors were set manually or are the result of CF.