Count Value of Cells that meet a certain condition

[cdhampshire]

Hi all,

I have a spreadsheet whereby I track the contracted hours of staff as well as whether that person will be in the office in any given week or not. Team members changes periodically as not two teams are the same size.

Im looking to see if there is a way that I can calculate the total combined hours of any team (despite any further changes) as well as checking how that number differs over the week.

I have attached a spreadsheet here and whislt I have not yet had a go any any formulas on it, I have added notes to the table (see the table with borders) with explanations of what I'd like. I appreciate that this explanation may not be clear but I really do appreciate any help

Thank you!

[AliGW]

In J4 copied down:

=SUMIF($A$4:$A$14,$I4,$C$4:$C$14)

In K4 copied across and down:

=SUMPRODUCT(($A$4:$A$14=$I4)*($D$2:$G$2=K$2),$D$4:$G$14)

[Kaper]

Alternative for K4 (and copy down and right):

Please Login or Register  to view this content.

PS. I wonder why you wrote 3 in J5. If it was rounding up, then of course you can do it also in Excel. Tri in J4 (and copy down):

Please Login or Register  to view this content.

and the same trick for K4 and right/down formula

[maclearyj]

Hi there. There may be more comprehensive answers, but if your workbook is laid out as you show, then put this formula in J4 and drag down, then across.
=SUMIF($A:$A,$I4,C:C)