Sumif with 2 Criteria

[BlueRhinos]

Objective: Trying to sum data values in a column that meet 2 conditions. Specifically, the values in column A that equal value D and values in column B that equal value E.
Here's my approach.

=SUM(('WMB Data - JULY'!$E$1:$E$642=A11)*('WMB Data - JULY'!$F$1:$F$642=A10)*'WMB Data - JULY'!$J$1:$J$642)

With CSE applied, I'm getting "#VALUE" and the message - "A value used in the formula is of the wrong data type". Can anyone elaborate and help me fix?

Any help is appreciated!

 

[VoG]

Try SUMPRODUCT instead of SUMIF (this is not an array formula so no need for CTRL + Shift + Enter.

 

[Jonmo1]

The formula works for me...

Either your values in column J are not real numbers (numbers stored as text)
OR
There are formula errors (#VALUE!) inside one or more cells the formula refers to.

 

[jakejackjacob]

My two cents, I'm certainly not an expert...

First try adding parentheses around the last range. Then, if that doesn't work, try entering "1" in a blank cell and paste special/multiply it down all of your numerical data columns.

Otherwise, wait for someone who knows more to answer...

JACK

 

[BlueRhinos]

Thanks all for the suggestions! As it turns out ,the formula didn't like the column header row being included in the criteria...when I modified the ranges to exclude it worked.

On a different note, I didn't realize sumproduct could help me accomplish. I actually used sumproduct to give me people counts that meant my criteria.

If anyone wants to elaborate on sumproduct's utility, feel free!

BR

 

[VoG]

There is more on SUMPRODUCT here http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

Thanks all for the suggestions! As it turns out ,the formula didn't like the column header row being included in the criteria...when I modified the ranges to exclude it worked.

The following...

[1]

=SUM(
   IF('WMB Data - JULY'!$E$1:$E$642=A11,
   IF'WMB Data - JULY'!$F$1:$F$642=A10,
    'WMB Data - JULY'!$J$1:$J$642)))

is insensitive against including the headers from the relevant ranges and faster than

[2]

=SUM(
   ('WMB Data - JULY'!$E$1:$E$642=A11)*
   ('WMB Data - JULY'!$F$1:$F$642=A10)*
    'WMB Data - JULY'!$J$1:$J$642)

On a different note, I didn't realize sumproduct could help me accomplish. I actually used sumproduct to give me people counts that meant my criteria.

If anyone wants to elaborate on sumproduct's utility, feel free!

BR

The SumProduct version like the one below...

[3]

=SUMPRODUCT(
   --('WMB Data - JULY'!$E$1:$E$642=A11),
   --('WMB Data - JULY'!$F$1:$F$642=A10)*
   'WMB Data - JULY'!$J$1:$J$642)

is quite similar to [2], but like [1] it ignores text values (like a header value) that might occur in the range to sum. This one is about as fast as [1].