SUMIF command with multiple criteria

[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!

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A11="Room1"),--(B1:B11="table"),C1:C11)

 

[Dave R.]

=SUMPRODUCT((A1:A11="Room1")*(B1:B11="Table"),D111)

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

 

[Ken Wright]

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

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

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!

 

[Tom Drill]

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

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!