count rows that meet certain criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a formula that will total all the payroll, overtime and training hours
for B and C in the below example:

A B C
1 payroll 15 8
2 overtime 5 0
3 payroll 6 22
4 training 4 4
5 training 8 11


payroll B = 21
overtime B = 5
training B = 12
payroll C = 30
overtime C = 0
training C = 15

i'm sure there is some easy formula that can do this, but i can't think of
one.
any suggestions?

thanks
 
Easiest would be to create a Pivot Table.

The harder way would be to use individual formulae:

payroll B: =SUMIF(A1:A5,"payroll",B1:B5)
payroll C: =SUMIF(A1:A5,"payroll",C1:C5)

etc.
 
payroll B =SUMIF($A$1:$A$5,"payroll",$B$1:$B$5)
overtime B =SUMIF($A$1:$A$5,"overtime",$B$1:$B$5)
training B =SUMIF($A$1:$A$5,"training",$B$1:$B$5)
payroll C =SUMIF($A$1:$A$5,"payroll",$C$1:$C$5)
overtime C =SUMIF($A$1:$A$5,"overtime",$C$1:$C$5)
training C =SUMIF($A$1:$A$5,"training",$C$1:$C$5)
 
thanks, the SUMIF formula worked perfectly!
unfortunatly the pivot table doesn't help in this situation.
 
Back
Top