SUMIF with multiple conditions
 

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?

SUMIF with multiple conditions
 

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),Am t)
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
---
"Michelle" wrote:
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?

SUMIF with multiple conditions
 

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!D1:D1000)

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


On Dec 11, 12:26*pm, Michelle
wrote:
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?

SUMIF with multiple conditions
 

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$10 00,0))

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

then copy down


On 11 Gru, 13:26, Michelle wrote:
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?

SUMIF with multiple conditions
 

On Thu, 11 Dec 2008 04:26:01 -0800, Michelle
wrote:

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)*(r ef_to_list_of_values))



--ron

SUMIF with multiple conditions
 

Thanks this one worked, and i learnt something knew :)


"Jarek Kujawa" wrote:

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$10 00,0))

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

then copy down


On 11 Gru, 13:26, Michelle wrote:
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?

SUMIF with multiple conditions
 

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
---
"Michelle" wrote:
Thanks this one worked, and i learnt something new :)

SUMIF with multiple conditions
 

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

On 11 Gru, 16:05, Max wrote:
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
Singaporehttp://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---



"Michelle" wrote:
Thanks this one worked, and i learnt something new :)- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

SUMIF with multiple conditions
 

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

"Max" wrote:

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
---
"Michelle" wrote:
Thanks this one worked, and i learnt something new :)