Forum Discussion
CountIfs by Color - Is this possible?
Trying to see if there are any gurus who can help me write a formula:
I have a rather large, financial report for a non-profit. Currently I'm tracing how much people are donating by year. When a donor stops giving I mark them in red; when we get a new donor, I mark them green. Right now I have a module added to my excel so that I can count/sum cells by color. However, I'd like to generate some more data; e.x. How many givers lost (marked in red) were giving 'x' amount.
Essentially, I'm looking for a 'CountIfs' (and Sumifs) but including color. i.e. countif(A2:A500,">2000",A2:A500,"cellcolor(red)")
Anyone have a solution?
1 Reply
Hello,
color is not data. When you manually set a cell color to give it a certain status, then that logic is inside your head, not in the Excel spreadsheet. There is no formula or function to extract the logic from your head.
A better way is to use another cell to enter the status as a word, a code, or a number. Then you can use conditional formatting to set the cell color based on that status cell. In addition to that, you can now easily use functions like Sumif, Sumifs, Countif or Countifs to analyse the data based on the value in the status column. Or quickly create a pivot table with just a few clicks and no formulas at all.
