SUMIF embedded with AND statements

**durfe1jp** · 09-03-2008, 10:51 AM

hello,

I need some help creating a sumif statement with an AND statement. I have enclosed a sample file that should answer any questions.

I have an employee that performs 2 job functions. (1=measure, 4=install.)

I have a summary that I want to add up the square footage for each job. So I would like a statement something along these lines:

sumif(a:a = 01smit AND c:c = 1 add up column B)

Thanks in advance.

**Ron Coderre** · 09-03-2008, 11:05 AM

Using your posted example...

This formula returns the total sq ft for the employee matching the left
side of the G5 heading and the pay code matching the right side of that
heading:

G7: =SUMPRODUCT(($A$6:$A$20=LEFT(G$5,
FIND(" ",G$5)-1))*($C$6:$C$20=--RIGHT(G$5,1))*$B$6:$B$20)

Here's the *hardcoded* version:

G7: =SUMPRODUCT(($A$6:$A$20="01smit")*($C$6:$C$20=4)*$B$6:$B$20)

In the sample sheet, both of those formulas return 60.
...but you can copy the first formula into cell H7 and it will return 25.

Is that something you can work with?