SUMIFS _ more different criteria from same criteria region

[aquilla]

Hello

Thing i would like to do in excel is this ..

I have a table with various numbers column and in another column are parameters describing these numbers. I want to sum all numbers which meet the required criteria .. something like this [TABLE="width: 150"]
<tbody>[TR]
[TD]numbers
[/TD]
[TD]parameter
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]C
[/TD]
[/TR]
</tbody>[/TABLE]

sum all numbers where parameters are A or C .. in another cell i want to sum all numbers where parameters are A, B, D.

I am not sure, if i am using wrong function or what .. SUMIF and SUMIFS works until I use one criteria like this

a) =SUMIFS(H1:H14; I1:I14; "A")

but when i add another criteria it shows zero

b) =SUMIFS(H1:H14; I1:I14; "A"; I1:I14; "B")

My questions are:
What and how to use to get results a want ?
Is there a way how to add multiple criteria in one criteria range ? When you look on (b) .. its waste of time and space to define criteria range for every new criteria when the range is not changing.

thank you for help and explaining !

 

[Akashwani]

Hi and welcome to MrExcel.

Does this work for you?...

Excel Workbook
	A	B	C	D	E
1	Numbers	Parameter		Result
2	5	B		64
3	2	B		64
4	5	A
5	6	D
6	5	A
7	3	B
8	5	A
9	35	C
10	33	B
11	30	C
12
Sheet10

If I'm not mistaken, the SUMIFS will not work as the Parameter can only be "true"/match once, to have multiple criteria, they would have to be in different columns. The Parameter can only ever be A or B or D it cannot be more than one in any single column.

I hope that helps.

Ak

 

[Weazel]

just to shorten it up a little you could try...

=SUMPRODUCT(SUMIFS(A2:A11,B2:B11,{"b","a","d"}))

or

=SUMPRODUCT(SUMIF(B2:B11,{"a","b","d"},A2:A11))

 

[aquilla]

Hi and welcome to MrExcel.

Does this work for you?...

Sheet10

*	A	B	C	D	E
*	*
B	*	*
B	*	*
A	*	*	*
D	*	*	*
A	*	*	*
B	*	*	*
A	*	*	*
C	*	*	*
B	*	*	*
C	*	*	*
*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:62px;"><col style="width:72px;"><col style="width:30px;"><col style="width:64px;"><col style="width:30px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Numbers[/TD]
[TD="align: center"]Parameter[/TD]

[TD="align: center"]Result[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]64[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]64[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]35[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]33[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =SUMIF($B$2:$B$11,"A",$A$2:$A$11)+SUMIF($B$2:$B$11,"B",$A$2:$A$11)+SUMIF($B$2:$B$11,"D",$A$2:$A$11) D3 =SUMPRODUCT(--($B$2:$B$11="A")+($B$2:$B$11="B")+($B$2:$B$11="D"),(A2:A11)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

If I'm not mistaken, the SUMIFS will not work as the Parameter can only be "true"/match once, to have multiple criteria, they would have to be in different columns. The Parameter can only ever be A or B or D it cannot be more than one in any single column.

I hope that helps.

Ak

wow thank you very much for quick reply and welcome )

It looks that it is working, i appologize i did not mention i am using office 2007,may be thats why i had to substitue "," with ";" in your formula, otherwise i got error message.

guess its solved

thank you again

 

[aquilla]

just to shorten it up a little you could try...

=SUMPRODUCT(SUMIFS(A2:A11,B2:B11,{"b","a","d"}))

or

=SUMPRODUCT(SUMIF(B2:B11,{"a","b","d"},A2:A11))

thank you weazel!! looks neat this way .. i will have to read about sumproduct function, it looks usefull

 

[Akashwani]

Hi aquilla.

I'm pleased it works for you and thanks for the feedback.

I may be wrong here and I hope that someone corrects me if I am, but I think the use of "," (comma) or ";" (Semicolon) depends on the language of your MS Office pack. I'm using English and I have seen many formulas on here where I have had to change the ";" to a "," for it to work for me.

Ak

 

[Aladin Akyurek]

=SUM(SUMIF(B2:B11,{"a","b","d"},A2:A11))

will do, unless the criterion values are in a range of their own like

X1: a
X2: b
X3: d

The latter case would require applying control+shift+enter to the current formula or replacing SUM with SUMPRODUCT.

 

[aquilla]

=SUM(SUMIF(B2:B11,{"a","b","d"},A2:A11))

will do, unless the criterion values are in a range of their own like

X1: a
X2: b
X3: d

The latter case would require applying control+shift+enter to the current formula or replacing SUM with SUMPRODUCT.

hmm thank you!! its interesting in how many different ways you can write "same formula" )