I have a set of transactions. The highlighted transactions are highlighted in yellow. I would like to use the SUMIF function to calculate the total. What I am using is SUMIF(D1:D107, "Yellow"). There is never enough time, thank you for yours. I would appreciate it if someone could show me how to do this. Is this possible?
If you are manually coloring cells in a column based on some event/state (like if a bill is due/paid/overdue or yes/no/maybe), instead of using the formatting tools you might consider using a column that has those events/states in a pop-up menu. That column would have a conditional format rule placed on it (maybe=yellow, yes=green, no=red). Select the item in the pop up menu and that cell will turn the appropriate color. The SUMIF would use that column of pop up menus to determine what cells to sum.
If your coloring is based on a binary condition (yes/no, true/false, paid/due), the new column could be a column of checkboxes rather than a column of pop up menus.
If you are manually coloring cells in a column based on some event/state (like if a bill is due/paid/overdue or yes/no/maybe), instead of using the formatting tools you might consider using a column that has those events/states in a pop-up menu. That column would have a conditional format rule placed on it (maybe=yellow, yes=green, no=red). Select the item in the pop up menu and that cell will turn the appropriate color. The SUMIF would use that column of pop up menus to determine what cells to sum.
If your coloring is based on a binary condition (yes/no, true/false, paid/due), the new column could be a column of checkboxes rather than a column of pop up menus.
Hi Evan,
Do you have a rule whereby you decide how to manually colour those cells?
If so, you can apply that rule to apply Conditional Highlighting to those cells. Then use the same rule in a SUMIF formula.
Please post a screen shot of the relevant part of your table. First, click on the table to show row (1, 2, 3...) and column (A, B, C...) labels to help us see what you see.
More information on your overall aim may lead to a solution.
Regards,
Ian.
No, it cannot be done. No formulas can access the background color or other formatting in a cell. Why do you think we suggested alternatives?
Unlike Excel which has interior.color, Apple's Numbers has no means to detect color in a cell. You could add a column adjacent to the color cells with a boolean True in it and then count the values in this example, cell A11 has the total matches in it.
Formula in A11:
Just tried this. Your solution doesn't work.
Are you aware we are your fellow users here?
You can give feedback to Apple via Numbers > Provide Numbers Feedback in your menu.
Keep in mind that Numbers has a distinct design philosophy and doesn't try to match all the functionality in other spreadsheet apps. Excel is great on the Mac, so you might want to use that if you like its features.
BTW, referring to a range like D1:D107 in Numbers isn't usually necessary. You can refer to an entire column simply with D.
SG
Thanks for the attempts. However, none of these solutions actually answer the question.
That's why I posted the question. We live in 2023, nobody has time to go through that. Apple *should* obviously add this as a feature since all the competing products provide this functionality.
You put TRUE next to each colored cell and it did not sum to the correct number?
How can you sum a set of numbers by their color using SUMIF?
