Sum by colour and other criteria

[Goddess]

Hi,

I have this table, where row 1 are names which will be grouped (based on the colour of the cell). I will need a subtotal based on the groups and then the grand total.

For example:
Subtotal 1 : B6=B3+D3+F3, C6=C3+E3+G3, etc

Subtotal 2 : F6=H3+J3, F7=H4+J4, etc

Same goes for Subtotal 3 and 4

Is there anyway to sum based on the colour, or maybe name the group as "Group1", Group 2", etc and then create a formula to sum accordingly? The groups are constantly changing.

Thanks!

 

[sxhall]

Hi Goddess(!)

Not sure if you found a result for this yet but you can use SUMIF if the groups have the same names for each colour. To get the different results I just offset the range for cell C6, see below images.

In cells B6 & F6 =SUMIF($B$1:$O$1,A$6,$B$3:$O$3)
In cells C6 & G6 =SUMIF($B$1:$O$1,A$6,$C$3:$P$3)
In cells B7 & F7 =SUMIF($B$1:$O$1,A$6,$B$4:$O$4)
In cells C7 & G7 =SUMIF($B$1:$O$1,A$6,$C$4:$P$4)

Hope this helps

Steven

 

[Goddess]

Hi Steven,

Thanks!