Would SUMIF be the correct formula to use in this situation

[adamacme]

Hi all,

I have a worksheet with 5 columns that has a list of values based off of bed/bath combinations. For Example:

A B C D E
Beds Baths Linens Consumables Totals
1 1 1 9.75 15.10 24.85

2 3 2 13.5 19.45 32.95

3 4 4 26.6 32.10 58.70

4 4 2 23.5 29.50 53.00

5 2 2 11.5 17.65 29.15


I have 2 cells in a row that have the specific Bed/Bath combo, and want to be able to give the total of the linens and consumables based off the two values.

so in another sheet I have:

Bed Bath
2 2

I have tried this VLOOKUP, but it doesn't seem to work: =VLOOKUP(Sheet2!I2:J2, Sheet2!$A$2:$F$34, 4, FALSE)

I think the problem is that I have two cells that need to be exactly matched in the above range, and VLOOKUP only seems to be able to successfully match one cell as the criteria.

So should I use the SUMIF Formula? And if so how would I type it out so it works? Thanks

 

[Jorge_B]

Hi,
I think you could accomplish that using the SUMIFS function (it depends on the version of Excel that you're using).
With your example, assuming that:
- Data is in Sheet1, range A1:E5
- Bed/Bath combo is in Sheet2 (starting at Row 2 for the headers.)

=SUMIFS(Sheet1!C$2:C$6;Sheet1!$A$2:$A$6;Sheet2!$A2;Sheet1!$B$2:$B$6;Sheet2!$B2)

It gives the total of linens, but you can drag it to fill consumables and totals if needed.

Hope it helps.

 

[Jorge_B]

Another option is to use SUMPRODUCT function.
Assuming that:
- Data is in Sheet1, range A1:E5
- Bed/Bath combo is in Sheet2 (starting at Row 2 for the headers.)

=SUMPRODUCT(--(Sheet1!$A$2:$A$6=Sheet2!$A2),--(Sheet1!$B$2:$B$6=Sheet2!$B2),Sheet1!C$2:C$6)

Just drag it to fill the comsumables and totals if needed.
Hope it helps.

 

[adamacme]

Another option is to use SUMPRODUCT function.
Assuming that:
- Data is in Sheet1, range A1:E5
- Bed/Bath combo is in Sheet2 (starting at Row 2 for the headers.)

=SUMPRODUCT(--(Sheet1!$A$2:$A$6=Sheet2!$A2),--(Sheet1!$B$2:$B$6=Sheet2!$B2),Sheet1!C$2:C$6)

Just drag it to fill the comsumables and totals if needed.
Hope it helps.

This worked, thanks!