SUMIF with multiple conditions

[Michelle]

Hi,

I need to do a sum of $ value, but there are 3 conditions to it, and those
conditions are written in the cells of a spreadsheet. So

Region Product Group
UK toys ABC
IE puzzles BCD
UK puzzles ABC

I have a long list, and the lookup sheet is on another sheet. I can't
hardcode the 3 conditions, it needs to reference the cells( which as you can
see change on every row). How do i do this?

 

[Max]

Assume you have identically sized defined names for the source data in the
other sheet, viz: Region, Product, Group and Amt

Assume your parameter table (as posted) is in A1:C4
Placed in say, D2:
=SUMPRODUCT((Region=A2)*(Product=B2)*(Group=C2),Amt)
will return the required sum for the params in A2:C2
Copy D2 down to return correspondingly for the other triplet params
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[Pete_UK]

Well, you will have to change the range references to suit the layout
of your data on the other sheet (assumed to be called "data"), but try
this:

=SUMPRODUCT((data!A1:A1000=A2)*(data!B1:B1000=B2)*(data!
C1:C1000=C2),data!D11000)

Put this in D2 of your summary sheet, and then copy down as required.
I have assumed that you want to sum column D from the data sheet.

Hope this helps.

Pete

 

[Jarek Kujawa]

A1=UK
B1=puzzles
C1=ABC

region in OtherSheet!$A$1:$A$1000
product in OtherSheet!$B$1:$B$1000
group in OtherSheet!$C$1:$C$1000
with values in OtherSheet!$D$1:$D$1000 use the formula

=SUM(IF((OtherSheet!$A$1:$A$1000=A1)*(OtherSheet!$B$1:$B$1000=B1)*
(OtherSheet!$C$1:$C$1000=C1),OtherSheet!$D$1:$D$1000,0))

CTRL+SHIFT+ENTER this formula as this is an array-formula

then copy down

 

[Ron Rosenfeld]

Hi,

I need to do a sum of $ value, but there are 3 conditions to it, and those
conditions are written in the cells of a spreadsheet. So

Region Product Group
UK toys ABC
IE puzzles BCD
UK puzzles ABC

I have a long list, and the lookup sheet is on another sheet. I can't
hardcode the 3 conditions, it needs to reference the cells( which as you can
see change on every row). How do i do this?

In Excel 2007 you can use the SUMIFS function.

In earlier versions, something like:

=sumproduct((region=A2)*(product=B2)*(group=C2)*(ref_to_list_of_values))



--ron

 

[Michelle]

Thanks this one worked, and i learnt something knew

 

[Max]

Michelle: To be fair to us "sumproduct" responders, our suggestion works
equally well, and doesn't require array-entering, to boot. The nuances &
styles in our responses may differ, but the sumproduct works, believe me.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[Jarek Kujawa]

Michelle, Max is right, SUMPRODUCT works well
nowadays SUMPRODUCT seem to have more followers, is simpler, my
laziness is the reason why I still use array-formulae

If my post was helpful pls click "YES"

thks

 

[Michelle]

Hi Max, I tried both methods, its simply that in this scenario when i applied
the Sumproduct it didn't work out. I certainly would have prefered an easier
formula- after all I have to maintain it too

Michelle