# SUMIFS _ more different criteria from same criteria region

#### aquilla

##### New Member
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
 numbers parameter 5 B 2 B 5 A 6 D 5 A 3 B 5 A 35 C 33 B 30 C

<tbody>
</tbody>

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 !

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Akashwani

##### Well-known Member
Hi and welcome to MrExcel.

Does this work for you?...

Excel Workbook
ABCDE
1NumbersParameterResult
25B64
32B64
45A
56D
65A
73B
85A
935C
1033B
1130C
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

##### Well-known Member
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

##### New Member
Hi and welcome to MrExcel.

Does this work for you?...

Sheet10

 * 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 * * * * *

<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>
</tbody>

 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

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP
=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

##### New Member
=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" )