I would like to build a formula that will evaluate the sum of a range of cells based on not only the category they relate to, but also on the colour that specific shells are shaded. I've attached an example spreadsheet of what I'm trying to do to help visualise this better.
Any help would be greatly appreciated.
heres UDF i came up with...its pretty specific to your example though
included the getbackground color UDF so you know where i get the random numbers from (which is colorindex)
Please Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
UDF
Use in cell B22
=SumCatCor($A22,$A$4:$M$18,B$21)
Fill right + down
Please Login or Register to view this content.
Last edited by jindon; 10-14-2015 at 11:35 PM.
Thanks humdingaling. I seem to be getting a 0 returned in the SumColor formula. Does the color index need to be a value, or can I simply concatenate the formulae together?
Thanks as well Jindon. I don't appear to have used the same colours in my original spreadsheet than the one I attached (my own fault). How do I obtain the different colour identifiers used in your UDF above?
You need to replace the Color Numbers
You could obtain color number:Please Login or Register to view this content.
1 Select the Colored cell that you want to get Color Number
2 Open VBE(Visual Basic Editor) Alt + F11
3 GoTo [View] - [ImmediateWindow]
4 type
?ActiveCell.Interior.Color
Then hit Enter key
It will give you color number that you want.
But this is not a smart way.
I would prefer to get the color from cell in the function like this
In cell B22:
=SumCatCor($A22,$A$4:$M$18,B$21)
Where B21 (Header) has colored like attached.
Please Login or Register to view this content.
yes colorindex needs to be a number
as per post #2, i included an additional UDF for you to get the colorindex
see row 30 of my sheet, i used getbackgroundcolor() UDF
the situation is similar to what Jindon Posts in post #6
however, Jindon's new code in post #6 has it built in as well as making the UDF react to changes within the sheet
i suggest you just take his version as it is a better version of what i had initially
That works a treat! Thanks for both of your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks