Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
Hello,
I have a set of data that has a label in column A the sales in column B, C & D: Column A Column B Column C Joel 500 677 Joel 575 752 Joel 650 827 Claudia 725 902 Tarzan 800 968 I'd like to run SUMIF where the "Range" is column A, the "Criteria" is Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C) I only get the sum of column B which is 1,725. Can someone hook me up with a formula that can sum both column B & C in this situation. Thanks, in advance Daniel |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
Try this approach:
=SUMPRODUCT((A2:A10="Joel")*B2:C10) Adjust range references to suit your situation. Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "dza7" wrote in message ... Hello, I have a set of data that has a label in column A the sales in column B, C & D: Column A Column B Column C Joel 500 677 Joel 575 752 Joel 650 827 Claudia 725 902 Tarzan 800 968 I'd like to run SUMIF where the "Range" is column A, the "Criteria" is Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C) I only get the sum of column B which is 1,725. Can someone hook me up with a formula that can sum both column B & C in this situation. Thanks, in advance Daniel |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
On Mar 14, 11:10*am, dza7 wrote:
Hello, I have a set of data that has a label in column A the sales in column B, C & D: Column A * * * *Column B * * * *Column C Joel * * * * * * * *500 * * * * * * * * *677 Joel * * * * * * * *575 * * * * * * * * *752 Joel * * * * * * * *650 * * * * * * * * *827 Claudia * * * * * 725 * * * * * * * * *902 Tarzan * * * * * *800 * * * * * * * * *968 I'd like to run SUMIF where the "Range" is column A, the "Criteria" is Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C) I only get the sum of column B which is 1,725. Can someone hook me up with a formula that can sum both column B & C in this situation. Thanks, in advance Daniel something something |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
On Mar 14, 11:14 am, "Ron Coderre"
wrote: Try this approach: =SUMPRODUCT((A2:A10="Joel")*B2:C10) Adjust range references to suit your situation. Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "dza7" wrote in message ... Hello, I have a set of data that has a label in column A the sales in column B, C & D: Column A Column B Column C Joel 500 677 Joel 575 752 Joel 650 827 Claudia 725 902 Tarzan 800 968 I'd like to run SUMIF where the "Range" is column A, the "Criteria" is Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C) I only get the sum of column B which is 1,725. Can someone hook me up with a formula that can sum both column B & C in this situation. Thanks, in advance Daniel Perfect, thank you very much! |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
You're very welcome....I'm glad I could help.
Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "dza7" wrote in message ... On Mar 14, 11:14 am, "Ron Coderre" wrote: Try this approach: =SUMPRODUCT((A2:A10="Joel")*B2:C10) Adjust range references to suit your situation. Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "dza7" wrote in message ... Hello, I have a set of data that has a label in column A the sales in column B, C & D: Column A Column B Column C Joel 500 677 Joel 575 752 Joel 650 827 Claudia 725 902 Tarzan 800 968 I'd like to run SUMIF where the "Range" is column A, the "Criteria" is Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C) I only get the sum of column B which is 1,725. Can someone hook me up with a formula that can sum both column B & C in this situation. Thanks, in advance Daniel Perfect, thank you very much! |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
"Ron Coderre" wrote in message ... Try this approach: =SUMPRODUCT((A2:A10="Joel")*B2:C10) I can never get my head round the SUMPRODUCT function. Can anyone explain in very simple terms(!!!) what it does :-) |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html gavin wrote: "Ron Coderre" wrote in message ... Try this approach: =SUMPRODUCT((A2:A10="Joel")*B2:C10) I can never get my head round the SUMPRODUCT function. Can anyone explain in very simple terms(!!!) what it does :-) -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
For a simple description, type the word SUMPRODUCT into Excel help. For
more detail, type the words SUMPRODUCT and Excel into Google. -- David Biddulph "gavin" wrote in message . .. "Ron Coderre" wrote in message ... Try this approach: =SUMPRODUCT((A2:A10="Joel")*B2:C10) I can never get my head round the SUMPRODUCT function. Can anyone explain in very simple terms(!!!) what it does :-) |
#9
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
I see that Dave Peterson gave you the xldynamic link, which is very
comprehensive. To augment that a bit, I'll describe the SUMPRODUCT functionality as it applies to formula in this thread. Let's see if this helps: SUMPRODUCT was *designed* to multiply two or more same-size ranges of cells and sum the products. Example (in cells A1:B4): Sale Rate 100 0.10 200 0.15 300 0.20 The commission would be the sum of each Sale x Rate. +(100 x 0.10) +(200 x 0.15) +(300 x 0.20) In this instance, the total happens to be 100. The SUMPRODUCT representation of that could be: =SUMPRODUCT(A2:A4,B2:B4) But SUMPRODUCT has an alternate structure, too: =SUMPRODUCT((A2:A4)*(B2:B4)) (Sometimes the second one works where the first one fails and vice versa) The function multiplies the A2 x B2 calcs 10.....adds to the total A3 x B3 calcs 30.....adds to the total A4 x B4 calcs 60.....adds to the total Commission = 100 (10+30+60) ********************************* Before continuing...Let's talk a bit about Boolean Values. That's a fancy term for True/False values. This formula: A1: =(5=5) returns TRUE....5 DOES equal 5 and This formula: A2: =(5=2) returns FALSE....5 does NOT equal 2 As such, TRUE and FALSE...are treated as WORDS by Excel. B1: =A1....returns TRUE and B2: =A2....returns FALSE BUT...When you apply an arithmetic operator (+, -, *, /) to a Boolean Value Excel converts TRUE to 1 and FALSE to 0. So... B1: =A1*1.....returns 1, instead of TRUE. B2: =A2*1.....returns 0, instead of FALSE. Actually, multiplying by any number converts TRUE to 1 and FALSE to 0. So...this would happen, too: B1: =A1*10.....returns 10. B2: =A2*10.....returns 0. ********************************** We'll exploit that feature in SUMPRODUCT..... In this formula: =SUMPRODUCT((A2:A10="Joel")*B2:C10) This expression: (A2:A10="Joel")....returns a series of TRUE/FALSE values depending on whether the cell equals "Joel" or not. and B2:C10 contains NUMBERS! SO.. Each TRUE when multiplied by its associated NUMBER returns that number. Each FALSE when multiplied by its associated NUMBER returns converts to a zero...0 x number = 0. SUMPRODUCT returns the sum of all those results which is the sum of all combinations where Col_A="Joel" Experiment with simple examples until you get comfortable with the way it works. I hope that helps. Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "gavin" wrote in message . .. "Ron Coderre" wrote in message ... Try this approach: =SUMPRODUCT((A2:A10="Joel")*B2:C10) I can never get my head round the SUMPRODUCT function. Can anyone explain in very simple terms(!!!) what it does :-) |
#10
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple columns in sum_range
"Ron Coderre" wrote in message ... I see that Dave Peterson gave you the xldynamic link, which is very comprehensive. To augment that a bit, I'll describe the SUMPRODUCT functionality as it applies to formula in this thread. Let's see if this helps: SUMPRODUCT was *designed* to multiply two or more same-size ranges of cells and sum the products. Example (in cells A1:B4): Sale Rate 100 0.10 200 0.15 300 0.20 The commission would be the sum of each Sale x Rate. +(100 x 0.10) +(200 x 0.15) +(300 x 0.20) In this instance, the total happens to be 100. The SUMPRODUCT representation of that could be: =SUMPRODUCT(A2:A4,B2:B4) But SUMPRODUCT has an alternate structure, too: =SUMPRODUCT((A2:A4)*(B2:B4)) (Sometimes the second one works where the first one fails and vice versa) The function multiplies the A2 x B2 calcs 10.....adds to the total A3 x B3 calcs 30.....adds to the total A4 x B4 calcs 60.....adds to the total Commission = 100 (10+30+60) ********************************* Before continuing...Let's talk a bit about Boolean Values. That's a fancy term for True/False values. This formula: A1: =(5=5) returns TRUE....5 DOES equal 5 and This formula: A2: =(5=2) returns FALSE....5 does NOT equal 2 As such, TRUE and FALSE...are treated as WORDS by Excel. B1: =A1....returns TRUE and B2: =A2....returns FALSE BUT...When you apply an arithmetic operator (+, -, *, /) to a Boolean Value Excel converts TRUE to 1 and FALSE to 0. So... B1: =A1*1.....returns 1, instead of TRUE. B2: =A2*1.....returns 0, instead of FALSE. Actually, multiplying by any number converts TRUE to 1 and FALSE to 0. So...this would happen, too: B1: =A1*10.....returns 10. B2: =A2*10.....returns 0. ********************************** We'll exploit that feature in SUMPRODUCT..... In this formula: =SUMPRODUCT((A2:A10="Joel")*B2:C10) This expression: (A2:A10="Joel")....returns a series of TRUE/FALSE values depending on whether the cell equals "Joel" or not. and B2:C10 contains NUMBERS! SO.. Each TRUE when multiplied by its associated NUMBER returns that number. Each FALSE when multiplied by its associated NUMBER returns converts to a zero...0 x number = 0. SUMPRODUCT returns the sum of all those results which is the sum of all combinations where Col_A="Joel" Experiment with simple examples until you get comfortable with the way it works. I hope that helps. Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) Oh no! I didn't understand a word of that - and I thought I was reasonably intelligent :-( |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple columns in sum_range
Hi Ron,
I wanted to thank you for your GREAT explanation: ********************************** We'll exploit that feature in SUMPRODUCT..... In this formula: =SUMPRODUCT((A2:A10="Joel")*B2:C10) This expression: (A2:A10="Joel")....returns a series of TRUE/FALSE values depending on whether the cell equals "Joel" or not. and B2:C10 contains NUMBERS! SO.. Each TRUE when multiplied by its associated NUMBER returns that number. Each FALSE when multiplied by its associated NUMBER returns converts to a zero...0 x number = 0. SUMPRODUCT returns the sum of all those results which is the sum of all combinations where Col_A="Joel" My question is this, I'm using Excel97, does this function work in this version? My formula =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) is not working. I thought I understood exactly what you were saying, maybe not..... -- TIA AFJr |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple columns in sum_range
Hi, AF Jr
If you're not getting a #NAME! error, then Excel 97 has the SUMPRODUCT function. There are 2 variations of the structure. If this one doesn't work: =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) Try this one: =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) Does that help? Post back if you have more questions. Oh, and thanks for the feedback on the explanation. Much appreciated. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, I wanted to thank you for your GREAT explanation: ********************************** We'll exploit that feature in SUMPRODUCT..... In this formula: =SUMPRODUCT((A2:A10="Joel")*B2:C10) This expression: (A2:A10="Joel")....returns a series of TRUE/FALSE values depending on whether the cell equals "Joel" or not. and B2:C10 contains NUMBERS! SO.. Each TRUE when multiplied by its associated NUMBER returns that number. Each FALSE when multiplied by its associated NUMBER returns converts to a zero...0 x number = 0. SUMPRODUCT returns the sum of all those results which is the sum of all combinations where Col_A="Joel" My question is this, I'm using Excel97, does this function work in this version? My formula =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) is not working. I thought I understood exactly what you were saying, maybe not..... -- TIA AFJr |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple columns in sum_range
Hi Ron,
Thanks for your quick response. I'm getting a #VALUE! error. Sorry for not posting that earlier. I'm getting this error using both structures of the formula. I'm stumped, any ideas? One more question, the "--" =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) what does that mean? I've tried it with and w/o that, still no luck. -- TIA AFJr "Ron Coderre" wrote: Hi, AF Jr If you're not getting a #NAME! error, then Excel 97 has the SUMPRODUCT function. There are 2 variations of the structure. If this one doesn't work: =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) Try this one: =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) Does that help? Post back if you have more questions. Oh, and thanks for the feedback on the explanation. Much appreciated. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, I wanted to thank you for your GREAT explanation: ********************************** We'll exploit that feature in SUMPRODUCT..... In this formula: =SUMPRODUCT((A2:A10="Joel")*B2:C10) This expression: (A2:A10="Joel")....returns a series of TRUE/FALSE values depending on whether the cell equals "Joel" or not. and B2:C10 contains NUMBERS! SO.. Each TRUE when multiplied by its associated NUMBER returns that number. Each FALSE when multiplied by its associated NUMBER returns converts to a zero...0 x number = 0. SUMPRODUCT returns the sum of all those results which is the sum of all combinations where Col_A="Joel" My question is this, I'm using Excel97, does this function work in this version? My formula =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) is not working. I thought I understood exactly what you were saying, maybe not..... -- TIA AFJr |
#14
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple columns in sum_range
The Double-Unary (--) forces a conversion of TRUE/FALSE values to numbers.
--TRUE = 1 --FALSE = 0 so...--{TRUE, TRUE, FALSE}...becomes {1,1,0} You could also multiply boolean values to the same effect: 1*TRUE = 1 1*FALSE = 0 But, the Dbl-Minus indicates to knowledgable users that a numeric conversion is intended, versus a calculation. Regarding your #VALUE! error....scan the referenced data and see if there are any errors or irregularities in it. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, Thanks for your quick response. I'm getting a #VALUE! error. Sorry for not posting that earlier. I'm getting this error using both structures of the formula. I'm stumped, any ideas? One more question, the "--" =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) what does that mean? I've tried it with and w/o that, still no luck. -- TIA AFJr "Ron Coderre" wrote: Hi, AF Jr If you're not getting a #NAME! error, then Excel 97 has the SUMPRODUCT function. There are 2 variations of the structure. If this one doesn't work: =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) Try this one: =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) Does that help? Post back if you have more questions. Oh, and thanks for the feedback on the explanation. Much appreciated. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, I wanted to thank you for your GREAT explanation: ********************************** We'll exploit that feature in SUMPRODUCT..... In this formula: =SUMPRODUCT((A2:A10="Joel")*B2:C10) This expression: (A2:A10="Joel")....returns a series of TRUE/FALSE values depending on whether the cell equals "Joel" or not. and B2:C10 contains NUMBERS! SO.. Each TRUE when multiplied by its associated NUMBER returns that number. Each FALSE when multiplied by its associated NUMBER returns converts to a zero...0 x number = 0. SUMPRODUCT returns the sum of all those results which is the sum of all combinations where Col_A="Joel" My question is this, I'm using Excel97, does this function work in this version? My formula =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) is not working. I thought I understood exactly what you were saying, maybe not..... -- TIA AFJr |
#15
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple columns in sum_range
Hi Ron,
Thanks for sticking with me through this. I've checked the data for errors. I'm not sure if this is a problem or not. The targeted cells in my formula: =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) B21:B62 are just text selected from a list. Q21:S62 contain formulas, columns Q & R formula is: =IF($E:$E=0,"",$E:$E*0) {the zero that is multiplied could be a value or zero} column S is: =IF($E:$E=0,"",(Q22+R22)*0.15) (the data in column "E" is a number that represents a "Qty" multiplier) So when the SUMPRODUCT function is executed it looks at column B to see if the value ="C" and produces a "1" if it is(True). If it evaluates to "1", columns Q:S formula's are executed and added together. Being that a zero could result in the the formula's in columns Q:S I suspect this is giving me my #VALUE! error. Multiplying any number by zero results in zero but, why would this be a problem? Maybe its the way excel evaluates it. If so, I've got to come up with another way to do this. Does this make sense to you? Any other suggestions? -- TIA AFJr "Ron Coderre" wrote: The Double-Unary (--) forces a conversion of TRUE/FALSE values to numbers. --TRUE = 1 --FALSE = 0 so...--{TRUE, TRUE, FALSE}...becomes {1,1,0} You could also multiply boolean values to the same effect: 1*TRUE = 1 1*FALSE = 0 But, the Dbl-Minus indicates to knowledgable users that a numeric conversion is intended, versus a calculation. Regarding your #VALUE! error....scan the referenced data and see if there are any errors or irregularities in it. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, Thanks for your quick response. I'm getting a #VALUE! error. Sorry for not posting that earlier. I'm getting this error using both structures of the formula. I'm stumped, any ideas? One more question, the "--" =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) what does that mean? I've tried it with and w/o that, still no luck. -- TIA AFJr "Ron Coderre" wrote: Hi, AF Jr If you're not getting a #NAME! error, then Excel 97 has the SUMPRODUCT function. There are 2 variations of the structure. If this one doesn't work: =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) Try this one: =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) Does that help? Post back if you have more questions. Oh, and thanks for the feedback on the explanation. Much appreciated. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, I wanted to thank you for your GREAT explanation: ********************************** We'll exploit that feature in SUMPRODUCT..... In this formula: =SUMPRODUCT((A2:A10="Joel")*B2:C10) This expression: (A2:A10="Joel")....returns a series of TRUE/FALSE values depending on whether the cell equals "Joel" or not. and B2:C10 contains NUMBERS! SO.. Each TRUE when multiplied by its associated NUMBER returns that number. Each FALSE when multiplied by its associated NUMBER returns converts to a zero...0 x number = 0. SUMPRODUCT returns the sum of all those results which is the sum of all combinations where Col_A="Joel" My question is this, I'm using Excel97, does this function work in this version? My formula =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) is not working. I thought I understood exactly what you were saying, maybe not..... -- TIA AFJr |
#16
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple columns in sum_range
It's time to try another approach:
Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of ENTER): =SUM(IF($B$21:$B$62="C",IF(ISNUMBER($Q$21:$S$62),$ Q$21:$S$62))) Does that help? ------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, Thanks for sticking with me through this. I've checked the data for errors. I'm not sure if this is a problem or not. The targeted cells in my formula: =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) B21:B62 are just text selected from a list. Q21:S62 contain formulas, columns Q & R formula is: =IF($E:$E=0,"",$E:$E*0) {the zero that is multiplied could be a value or zero} column S is: =IF($E:$E=0,"",(Q22+R22)*0.15) (the data in column "E" is a number that represents a "Qty" multiplier) So when the SUMPRODUCT function is executed it looks at column B to see if the value ="C" and produces a "1" if it is(True). If it evaluates to "1", columns Q:S formula's are executed and added together. Being that a zero could result in the the formula's in columns Q:S I suspect this is giving me my #VALUE! error. Multiplying any number by zero results in zero but, why would this be a problem? Maybe its the way excel evaluates it. If so, I've got to come up with another way to do this. Does this make sense to you? Any other suggestions? -- TIA AFJr "Ron Coderre" wrote: The Double-Unary (--) forces a conversion of TRUE/FALSE values to numbers. --TRUE = 1 --FALSE = 0 so...--{TRUE, TRUE, FALSE}...becomes {1,1,0} You could also multiply boolean values to the same effect: 1*TRUE = 1 1*FALSE = 0 But, the Dbl-Minus indicates to knowledgable users that a numeric conversion is intended, versus a calculation. Regarding your #VALUE! error....scan the referenced data and see if there are any errors or irregularities in it. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, Thanks for your quick response. I'm getting a #VALUE! error. Sorry for not posting that earlier. I'm getting this error using both structures of the formula. I'm stumped, any ideas? One more question, the "--" =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) what does that mean? I've tried it with and w/o that, still no luck. -- TIA AFJr "Ron Coderre" wrote: Hi, AF Jr If you're not getting a #NAME! error, then Excel 97 has the SUMPRODUCT function. There are 2 variations of the structure. If this one doesn't work: =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) Try this one: =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62) Does that help? Post back if you have more questions. Oh, and thanks for the feedback on the explanation. Much appreciated. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "AFJr" wrote in message ... Hi Ron, I wanted to thank you for your GREAT explanation: ********************************** We'll exploit that feature in SUMPRODUCT..... In this formula: =SUMPRODUCT((A2:A10="Joel")*B2:C10) This expression: (A2:A10="Joel")....returns a series of TRUE/FALSE values depending on whether the cell equals "Joel" or not. and B2:C10 contains NUMBERS! SO.. Each TRUE when multiplied by its associated NUMBER returns that number. Each FALSE when multiplied by its associated NUMBER returns converts to a zero...0 x number = 0. SUMPRODUCT returns the sum of all those results which is the sum of all combinations where Col_A="Joel" My question is this, I'm using Excel97, does this function work in this version? My formula =SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62) is not working. I thought I understood exactly what you were saying, maybe not..... -- TIA AFJr |
#17
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple columns in sum_range
Ron,
You are a godsend! Yes that worked beautifully. I was ready to move on and try something else but really had no idea. Again, I really appreciate you sticking with me, trying different things and such. Helping me to open my mind. I love user groups, a place to give help and get help. Thanks again Ron, Have a great weekend! AFJr "Ron Coderre" wrote: It's time to try another approach: Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of ENTER): =SUM(IF($B$21:$B$62="C",IF(ISNUMBER($Q$21:$S$62),$ Q$21:$S$62))) Does that help? ------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) |
#18
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif with multiple columns in sum_range
You're very welcome.....I'm glad I could help.
Regards, Ron Microsoft MVP (Excel) "AFJr" wrote in message ... Ron, You are a godsend! Yes that worked beautifully. I was ready to move on and try something else but really had no idea. Again, I really appreciate you sticking with me, trying different things and such. Helping me to open my mind. I love user groups, a place to give help and get help. Thanks again Ron, Have a great weekend! AFJr "Ron Coderre" wrote: It's time to try another approach: Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of ENTER): =SUM(IF($B$21:$B$62="C",IF(ISNUMBER($Q$21:$S$62),$ Q$21:$S$62))) Does that help? ------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum if -- Sum_range across multiple columns | Excel Discussion (Misc queries) | |||
SUMIF - Sum_Range is misleading | Excel Worksheet Functions | |||
SUMIF with the Sum_range across several colums | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
SUMIF with multi-column sum_range | Excel Worksheet Functions |