SUMIF command with multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

With the given workbook

A B C(cost) D
1 Room1 table 10 Joh
2 Room1 table 12 Joh
3 Room2 table 12 Jan
4 Room1 table Jan
5 Room3 table 10 Joh
6 Room1 chair 5 Jan
7 Room1 chair 8 Jan
8 Room2 chair Jan
9 Room3 chair 5 Jan
10 Room1 desk 12 Joh
11 Room2 desk Joh

.... I need a SUMIF formula that will look for all the Room1's in Column A that have a corresponding "table" entry in Column B and sum them so that they return the answer 22. I've been struggling with this for day now. Please help!
 
=SUMPRODUCT((A1:A11="Room1")*(B1:B11="Table"),D1:D11)


Tom Drill said:
With the given workbook:

A B C(cost) D E
1 Room1 table 10 John
2 Room1 table 12 John
3 Room2 table 12 Jane
4 Room1 table Jane
5 Room3 table 10 John
6 Room1 chair 5 Jane
7 Room1 chair 8 Jane
8 Room2 chair Jane
9 Room3 chair 5 Jane
10 Room1 desk 12 John
11 Room2 desk John

... I need a SUMIF formula that will look for all the Room1's in Column A
Click to expand...
that have a corresponding "table" entry in Column B and sum them so that
they return the answer 22. I've been struggling with this for day now.
Please help!
 
Take a look at the SUMPRODUCT function to do multiple criteria evaluation, eg:-

=SUMPRODUCT((A1:A100="Room1")*(B1:B100="Table")*(C1:C100))

or save yourself a whole lot of grief and use a pivot table to analyse your
data, which will group and sum all your data for all the individual elements in
about 60 seconds. For a good intro:-

http://peltiertech.com/Excel/Pivots/pivotstart.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Tom Drill said:
With the given workbook:

A B C(cost) D E
1 Room1 table 10 John
2 Room1 table 12 John
3 Room2 table 12 Jane
4 Room1 table Jane
5 Room3 table 10 John
6 Room1 chair 5 Jane
7 Room1 chair 8 Jane
8 Room2 chair Jane
9 Room3 chair 5 Jane
10 Room1 desk 12 John
11 Room2 desk John

... I need a SUMIF formula that will look for all the Room1's in Column A that
Click to expand...
have a corresponding "table" entry in Column B and sum them so that they return
the answer 22. I've been struggling with this for day now. Please help!
 
Thanks for everyone's help. I was just coming back to
post my answer when I saw the existing answers. Thanks
gang! The answer I came up with was:

=SUM((A1:A11="Room1")*(B1:B11="table")*C1:C11)

....as an array formula.

Tom Drill
-----Original Message-----
With the given workbook:

A B C(cost) D E
1 Room1 table 10 John
2 Room1 table 12 John
3 Room2 table 12 Jane
4 Room1 table Jane
5 Room3 table 10 John
6 Room1 chair 5 Jane
7 Room1 chair 8 Jane
8 Room2 chair Jane
9 Room3 chair 5 Jane
10 Room1 desk 12 John
11 Room2 desk John

.... I need a SUMIF formula that will look for all the
Click to expand...
Room1's in Column A that have a corresponding "table"
entry in Column B and sum them so that they return the
answer 22. I've been struggling with this for day now.
Please help!
 
Back
Top