Sum column based on multiple criteria in another column

[CNN]

Hello,

I typed the formula below in one of my excel worksheets and it worked great. I dragged it across, no problem. But when I try to copy it to another cell, I get an error. If I insert a new row below the row where I typed the formula all the cells above change into the #VALUE error. Please help.

=SUM(SUMIFS('File PathSummary'!D:D,'File PathSummary'!$C:$C,{131,132,133,138}))

Apparently this is common when using SUMIFS in excel 97-2003 worksheets.

So I tried

= SUM(IF('File PathSummary'!$C:$C={131,132,133,138}, 'File PathSummary'!D:D,0))

But it is an array function and I want to avoid that.

Is there any other thing I can do?

Thanks in advance.

[AlKey]

Try it like this

Please Login or Register  to view this content.

You may want to upload an excel file to speed up the process

[Tony Valko]

I dragged it across, no problem. But when I try to copy it to another cell, I get an error.

Dragging it across IS copying it to another cell.

Don't know why you'd get an error.

Apparently this is common when using SUMIFS in excel 97-2003 worksheets.

SUMIFS is not available in Excel versions 97-2003. It was introduced in Excel 2007.

[CNN]

Try it like this

Please Login or Register  to view this content.

You may want to upload an excel file to speed up the process

Sorry about that. The file contains some confidential info.

And SUMIF has that same problem with SUMIFS. If the source file is not open, I get the #VALUE error when writing the formula.

[Tony Valko]

Use SUMPRODUCT...

=SUMPRDOUCT(--ISNUMBER(MATCH('File PathSummary'!$C1:$C100,{131,132,133,138},0)),'File PathSummary'!D1:D100)

You should avoid using entire columns as range references in the SUMPRODUCT function. Use smaller specific ranges.

[CNN]

Use SUMPRODUCT...

=SUMPROUCT(--ISNUMBER(MATCH('File PathSummary'!$C1:$C100,{131,132,133,138},0)),'File PathSummary'!D1:D100)

You should avoid using entire columns as range references in the SUMPRODUCT function. Use smaller specific ranges.

Thank you so much. This worked out perfectly.

I'm still new to Excel on this level. Could you please explain what the formula does?

[CNN]

Use SUMPRODUCT...

=SUMPROUCT(--ISNUMBER(MATCH('File PathSummary'!$C1:$C100,{131,132,133,138},0)),'File PathSummary'!D1:D100)

You should avoid using entire columns as range references in the SUMPRODUCT function. Use smaller specific ranges.

Thank you so much. This worked out perfectly.

I'm still new to Excel on this level. Could you please explain what the formula does?

[Tony Valko]

See if this helps...

http://xldynamic.com/source/xld.SUMPRODUCT.html

[CNN]

I'm still going through the page you sent me. It's very interesting. I was reading and practising the MATCH function and I noticed that the syntax is MATCH(lookup_value, lookup_array, [match_type]). I was wondering why your formula worked.

[Tony Valko]

Let's see how this works using a small sample...

Data Range

	A	B	C	D
1	X	30		118
2	Y	41
3	Z	71
4	Z	17
5	Y	73
6	------	------	------	------

This formula entered in D1:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A5,{"X","Z"},0)),B1:B5)

MATCH returns the position number of the lookup value RELATIVE to the lookup array if the lookup value is found. If the lookup value is not found then MATCH returns the #N/A error value.

This is how that would look:

A1: MATCH("X",{"X","Z"},0) = 1
A2: MATCH("Y",{"X","Z"},0) = #N/A
A3: MATCH("Z",{"X","Z"},0) = 2
A4: MATCH("Z",{"X","Z"},0) = 2
A5: MATCH("Y",{"X","Z"},0) = #N/A

We then test to see if MATCH found the lookup value by testing the result as being a number and not the #N/A error:

A1: ISNUMBER(1) = TRUE
A2: ISNUMBER(#N/A) = FALSE
A3: ISNUMBER(2) = TRUE
A4: ISNUMBER(2) = TRUE
A5: ISNUMBER(#N/A) = FALSE

SUMPRODUCT works with numbers so we have to convert those TRUE and FALSE to numbers. One way to do that is using the double unary minus --.

--TRUE = 1
--FALSE = 0

A1: --TRUE = 1
A2: --FALSE = 0
A3: --TRUE = 1
A4: --TRUE = 1
A5: --FALSE = 0

This array of 1s and 0s is then multiplied with the values in B1:B5...

1 * 30 = 30
0 * 41 = 0
1 * 71 = 71
1 * 17 = 17
0 * 73 = 0

These results are then summed for the final result of the formula:

SUMPRODUCT({30;0;71;17;0}) = 118

So:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A5,{"X","Z"},0)),B1:B5) = 118

[CNN]

Thanks. When I read the article you sent to me, I understood your calculation. The only part that stumped me was the fact the the syntax for the MATCH function seems to be switched.
The syntax is MATCH(lookup_value, lookup_array, [match_type])
Yours appears to be MATCH(lookup_array, lookup_value, [match_type]).

I might be mistaken but I though the lookup_ array was supposed to be the column we were searching and the look_up values were the ones I specified in the brackets.

[Tony Valko]

I might be mistaken but I though the lookup_ array was supposed to be the column we were searching and the look_up values were the ones I specified in the brackets.

No, it's the other way around.

The lookup values are the range values A1:A5.

The lookup array are the values in the brackets {"X","Z"}.

[CNN]

OK but if was search for just Y in column A, will the function remain as written or will it now be

MATCH("Y", A1:A5,0)

[Tony Valko]

It would still be:

MATCH(A1:A5,{"Y"},0)

However, if there is just a single criteria to match then we would do it like this:

=SUMPRODUCT(--(A1:A5="Y"),B1:B5)

[CNN]

Hmm. I tried using MATCH(A1:A5,{"Y"},0) but it didn't work. But MATCH("Y", A1:A5,0) worked.

Oh well, I'll go back and study some more. Thanks for all your help.

[Tony Valko]

MATCH("Y", A1:A5,0)

Will return the position number of the lookup value "Y" within the range A1:A5.

Based on the table below that result would be 2.

Data Range

	A	B	C
1	X	30	114
2	Y	41
3	Z	71
4	Z	17
5	Y	73
6	------	------	------

However, in your application where you want a conditional sum it's the other way around:

Either one of these formulas entered in C1:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A5,{"Y"},0)),B1:B5)

=SUMPRODUCT(--(A1:A5="Y"),B1:B5)

[CNN]

Ok so if I am using the MATCH function as a standalone function, I use one syntax but if I am using it to get a conditional sum, I switch the syntax? That's kind of confusing.

[Tony Valko]

Yeah, it can be confusing until you get the hang of it.