Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 :) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Sumif conditions | Excel Worksheet Functions | |||
Using SumIf and Multiple Conditions | Excel Discussion (Misc queries) | |||
SumIF-multiple conditions/OR | New Users to Excel | |||
sumif with multiple conditions | Excel Worksheet Functions | |||
sumif with multiple conditions | Excel Worksheet Functions |