SUMIFS _ more different criteria from same criteria region

aquilla

New Member
Joined
May 12, 2013
Messages
4
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 !
 

Some videos you may like

Excel Facts

Excel Can Read to You
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
Joined
Mar 14, 2009
Messages
2,911
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
Joined
Dec 24, 2011
Messages
3,155
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
Joined
May 12, 2013
Messages
4
Hi and welcome to MrExcel.

Does this work for you?...

Sheet10

*
A
BCDE
1NumbersParameter*Result*
25B*64*
32B*
64*
45A**
*
56D***
65A***
73B***
85A***
935C***
1033B***
1130C***
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>

Spreadsheet Formulas
CellFormula
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
Joined
May 12, 2013
Messages
4
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
Joined
Mar 14, 2009
Messages
2,911
Hi aquilla.

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

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,154
=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
Joined
May 12, 2013
Messages
4
=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" :))
 

Watch MrExcel Video

Forum statistics

Threads
1,090,552
Messages
5,415,239
Members
403,574
Latest member
stdar2

This Week's Hot Topics

Top