| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"SUMPRODUCTIF"
I find myself asking a lot of questions on these boards lately... Thank you
to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 .... I would want to return [(2*6) + (4*12)] = 60. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
Try something like this:
=SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
=SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5)
Tyro "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
That seems to be doing exactly what I needed... What do the two minus signs
(--) preceding that argument do? I did a little searching around, and it looks like they... multiply by 1... thereby doing something with the format? I'm still lost! "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
In this formula
=SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10) (A1:A10="YELLOW") returns a series of TRUE/FALSE values which are NOT numeric, so SUMPRODUCT can't use them "as is" However, in Excel, when TRUE/FALSE values are acted upon by an arithmetic operator (+, -, *, / ) it converts TRUE to 1 and FALSE to 0 We use the Dbl-Minus as an unambiguous way of indicating that we are forcing a numeric conversion. It works this way: TRUE = TRUE -TRUE = -1 --TRUE = 1 So this series: --(TRUE; FALSE; TRUE; TRUE) becomes 1; 0; 1; 1 Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "PaladinWhite" wrote in message ... That seems to be doing exactly what I needed... What do the two minus signs (--) preceding that argument do? I did a little searching around, and it looks like they... multiply by 1... thereby doing something with the format? I'm still lost! "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
The first "-" coerces Excel to change the logical value of TRUE to its
numeric equivalent of 1. But the "-", makes the TRUE -1. The second "-" makes the -1 a 1. Simple algebra, -(-1) = 1. As for the logical value FALSE which is 0, the application of "--" has no meaning since 0 is neither positive or negative and remains 0 In Excel a 0 is FALSE and all other values are TRUE. Tyro "PaladinWhite" wrote in message ... That seems to be doing exactly what I needed... What do the two minus signs (--) preceding that argument do? I did a little searching around, and it looks like they... multiply by 1... thereby doing something with the format? I'm still lost! "Ron Coderre" wrote: Try something like this: =SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10) Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
Hey, that works too! Thanks, Tyro.
"Tyro" wrote: =SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5) Tyro "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
Simple matter of coersion, "--" or "*" in this case.
Tyro "PaladinWhite" wrote in message ... Hey, that works too! Thanks, Tyro. "Tyro" wrote: =SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5) Tyro "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
You've both explained it very well... I see that the -- is simply allowing
the value of the next statement to act as an "eliminator" of sorts so I only get multiplication through on the rows that I wanted. I can already think of other places where I can implement that trick. Thanks again. "Tyro" wrote: Simple matter of coersion, "--" or "*" in this case. Tyro "PaladinWhite" wrote in message ... Hey, that works too! Thanks, Tyro. "Tyro" wrote: =SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5) Tyro "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
"SUMPRODUCTIF"
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "PaladinWhite" wrote in message ... You've both explained it very well... I see that the -- is simply allowing the value of the next statement to act as an "eliminator" of sorts so I only get multiplication through on the rows that I wanted. I can already think of other places where I can implement that trick. Thanks again. "Tyro" wrote: Simple matter of coersion, "--" or "*" in this case. Tyro "PaladinWhite" wrote in message ... Hey, that works too! Thanks, Tyro. "Tyro" wrote: =SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5) Tyro "PaladinWhite" wrote in message ... I find myself asking a lot of questions on these boards lately... Thank you to all you great people that make the time to help! I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would do - the catch is that I only want to multiply pairs where the corresponding cell in Column A contains the text "YELLOW". For instance, in this case: PURPLE | 1 | 3 YELLOW | 2 | 6 PURPLE | 3 | 9 YELLOW | 4 | 12 PURPLE | 5 | 15 ... I would want to return [(2*6) + (4*12)] = 60. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
