array sum based on three conditions

[richardg]

I've tried searching the board but couldn't find anything. (or maybe I couldn't understand what I found!)

I'm trying to add up hours for employess in a spread sheet based on name, work type (regular, Overtime, etc) , and month. So, I have three array conditions and one to add up.

A B C D
1joe OT 1 8
2joe RE 2 8
3jim RE 1 12
4jim OT 1 8

I tried sum(if(and(A1:A4="joe",B1:B4="OT",C1:C4=1),D1:D4))

any ideas?

 

[Oaktree]

Welcome to the board.

=SUMPRODUCT(--(A1:A4="Joe"),--(B1:B4="OT"),--(C1:C4=1),D1:D4)

 

[Scott Huish]

Hi, welcome to the board!

As a formula, something like this:

=SUMPRODUCT(--(A1:A4="Joe"),--(B1:B4="OT"),--(C1:C4=1),D1:D4)

but since you're summing for all employees, have you tried a Pivot Table?

 

[richardg]

thanks. formula works.....as you already knew.

My first attempt at pivot table was over this problem so I didn't get very far.

What the heck is -- anyway?

 

[Domenic]

What the heck is -- anyway?

Have a look here.