Thread: SUMIF - add values from one column based on values in two other columns

I see similar questions and solutions, but I keep getting a error using the following that I used from another example in this forum.

SUMPRODUCT(--(A2:A15=A21);--(B2:B15=B21)@;N2:N15)

I am referencing other sheets in the workbook and I can't get past the inserted @ above.

My columns are :

Hours JOB Date
8 ci 2/6
6 ex 2/6
8 ci 2/7
6 ci 2/6



I want to add the hours of job ci on Feb 6th. should be 14.

Thanks, mike

Good morning goodmike

If your Hours are in A, the Job in B and the Date in C and you want to add column A, based on the values in B21 and C21 then this formula will help :
=SUMPRODUCT(--(B2:B15=B21),--(C2:C15=C21),(A2:A15))

HTH

DominicB

I was able to use this formula today to help me with a spreadsheet.

I have a question though. What does the double dash '--' signify in the formula? The formula doesn't work without it so it has to do something, correct?

B2:B15=B21 returns an array of TRUE/FALSE values. -- converts these to 1/0 values which SUMPRODUCT can then deal with. There are other ways to do that conversion, e.g. you can use

=SUMPRODUCT((B2:B15=B21)+0,(C2:C15=C21)+0,A2:A15)

...or a slightly different syntax.....

=SUMPRODUCT((B2:B15=B21)*(C2:C15=C21),A2:A15)

For more information on uses of SUMPRODUCT see here

thanks for the answer. I've used the "*" before, but never seen the "--".

That link is also very helpful in understanding sumproduct.

thanks!

-joel