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?

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?

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?

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?

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?

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

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 -

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

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