I have a list of times with sum being green (good) and some being red(bad). I need to total just the red ones to get the total time. I keep getting a name error and I cant figure out why?
I have a list of times with sum being green (good) and some being red(bad). I need to total just the red ones to get the total time. I keep getting a name error and I cant figure out why?
There is no SUMBYCOLOUR function in Excel - you need a UDF, and there is no UDF defined in the workbook.
I am moving this to the VBA section for you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Chris,
Since you have criteria to color a cell in column M either Red or Green, use that same criteria to Sum. No need for a UDF or VBA.
Green >> R11 =SUMIFS(M3:M19,M3:M19,">0",M3:M19,"<0.00694444444444444")
Red >> R12 =SUM(M3:M19)-R11
HTH
Regards, Jeff
I have the module populated with the UDF but it is still not working.
That code cannot detect cells coloured by CF.
Why not use the formulae suggested by Jeff? It's a lot easier.
And it will never work because the interior color of cell R11 is not the same as the interior color of the "Red" colored cells in Column M.
Column M is "Red" or "Green" thru conditional formatting. The color is not the same.
For your testing, put some numbers in T7:T9. Now in T11 >> =sumbycolor(R11,T7:T9)
Why not use the formulas I posted?
the criteria is going to change. I thought I could do (conditional formatting based on the value of another cell) AND using color would be the easiest way. how can I make your formula point to a cell to get the criteria rather than listing the criteria in the formula and having to always change it?
Try
=SUMIFS(M3:M19,M3:M19,">"&A1,M3:M19,"<"&A2)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks