Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hey all need some help correcting this formula
=SUMPRODUCT(--(E32:AJ32="x"),--(F32:AK32<""))*20+C32 How it works is follow Correct pick =checkmark Incorrect pick = X If X is followed by anther selection then multiple it by $20 The problem Im having is if the result is X and I skip one cell its not adding the $20 Even if I skip one cell it should add the $20 if another pick is adding along this cell area. Thanks ahead |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hi,
we need to know more, what do you mean Correct pick = checkmark? What is actually in the cells on a given row? You say add 20 but then you are multiplying by 20, which are you trying to do? Show us a line of sample data. For example, if cell E32 contains X and the next cell contains checkmark, whatever that is, should we add $20. If there are four X's in a row do we add 3*20 or just 20. One problem for example suppose AJ32 has an X, then technically there is only one cell that follows it that can be <"" but your formula checks the entire range from F32:AK32. So if F32 is <"" but does not contain an X but AJ32 contains an X this formula pays $20, is that what you want? -- Thanks, Shane Devenshire "William17" wrote: Hey all need some help correcting this formula =SUMPRODUCT(--(E32:AJ32="x"),--(F32:AK32<""))*20+C32 How it works is follow Correct pick =checkmark Incorrect pick = X If X is followed by anther selection then multiple it by $20 The problem Im having is if the result is X and I skip one cell its not adding the $20 Even if I skip one cell it should add the $20 if another pick is adding along this cell area. Thanks ahead |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
The formula is listed in cell AL32
The Cell Starts from D32 and ends at AK32 Cell D32 has a team name if correctly picked Cell E32 will have a Checkmark If not Correctly pick E32 will have a X, If Cell E32 has an X and a team name in Listed in Cell F32 then $20 must be added to Cell AL32. If Cell E32 Has an X but no Team name in Cell F32 then nothing should be added to cell AL32 If Cell E32 Has an X and cell E32 is blank But Cell H32 has a team then Cell AL32 should have $20 Example Cell D E F G H I J K L M N O Row 32 DEN ˆš TOR ˆš DET X SOM ˆš VAN X WSH ˆš AL32 Total should be: $40 Cell D E F G H I J K L M N O Row 33 DEN X TOR X SOM ˆš CGY X WSH ˆš AL32 Total should be: $60 Thanks "ShaneDevenshire" wrote: Hi, we need to know more, what do you mean Correct pick = checkmark? What is actually in the cells on a given row? You say add 20 but then you are multiplying by 20, which are you trying to do? Show us a line of sample data. For example, if cell E32 contains X and the next cell contains checkmark, whatever that is, should we add $20. If there are four X's in a row do we add 3*20 or just 20. One problem for example suppose AJ32 has an X, then technically there is only one cell that follows it that can be <"" but your formula checks the entire range from F32:AK32. So if F32 is <"" but does not contain an X but AJ32 contains an X this formula pays $20, is that what you want? -- Thanks, Shane Devenshire "William17" wrote: Hey all need some help correcting this formula =SUMPRODUCT(--(E32:AJ32="x"),--(F32:AK32<""))*20+C32 How it works is follow Correct pick =checkmark Incorrect pick = X If X is followed by anther selection then multiple it by $20 The problem Im having is if the result is X and I skip one cell its not adding the $20 Even if I skip one cell it should add the $20 if another pick is adding along this cell area. Thanks ahead |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hi,
I lost this email notification, sorry. Here are some ideas, not a solution because I couldn't make anything out of the 4th conditions if E32 is blank and E32 has an X then??? Further what has H32 got to do with anything? Further more you seem to be saying if E32 has a X and there is a team name before or after it then $20 - (Rule 1 and 2). If this is really the case you may want to consider redesigning you spreadsheet, because it doesn't lend itself to the formula work. Anyway from the data you provided it looks like if there is an X then $20 in which case the formula would be =SUMPRODUCT(--(A2:K2="X")*20) Adjusted for the appropriate range. -- Thanks, Shane Devenshire "William17" wrote: The formula is listed in cell AL32 The Cell Starts from D32 and ends at AK32 Cell D32 has a team name if correctly picked Cell E32 will have a Checkmark If not Correctly pick E32 will have a X, If Cell E32 has an X and a team name in Listed in Cell F32 then $20 must be added to Cell AL32. If Cell E32 Has an X but no Team name in Cell F32 then nothing should be added to cell AL32 If Cell E32 Has an X and cell E32 is blank But Cell H32 has a team then Cell AL32 should have $20 Example Cell D E F G H I J K L M N O Row 32 DEN ˆš TOR ˆš DET X SOM ˆš VAN X WSH ˆš AL32 Total should be: $40 Cell D E F G H I J K L M N O Row 33 DEN X TOR X SOM ˆš CGY X WSH ˆš AL32 Total should be: $60 Thanks "ShaneDevenshire" wrote: Hi, we need to know more, what do you mean Correct pick = checkmark? What is actually in the cells on a given row? You say add 20 but then you are multiplying by 20, which are you trying to do? Show us a line of sample data. For example, if cell E32 contains X and the next cell contains checkmark, whatever that is, should we add $20. If there are four X's in a row do we add 3*20 or just 20. One problem for example suppose AJ32 has an X, then technically there is only one cell that follows it that can be <"" but your formula checks the entire range from F32:AK32. So if F32 is <"" but does not contain an X but AJ32 contains an X this formula pays $20, is that what you want? -- Thanks, Shane Devenshire "William17" wrote: Hey all need some help correcting this formula =SUMPRODUCT(--(E32:AJ32="x"),--(F32:AK32<""))*20+C32 How it works is follow Correct pick =checkmark Incorrect pick = X If X is followed by anther selection then multiple it by $20 The problem Im having is if the result is X and I skip one cell its not adding the $20 Even if I skip one cell it should add the $20 if another pick is adding along this cell area. Thanks ahead |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
I thought I would add this modification in case it gave you any ideas:
=SUMPRODUCT(--(B2:K2="ˆš"),--(A2:J2<""))*20 -- Thanks, Shane Devenshire "William17" wrote: The formula is listed in cell AL32 The Cell Starts from D32 and ends at AK32 Cell D32 has a team name if correctly picked Cell E32 will have a Checkmark If not Correctly pick E32 will have a X, If Cell E32 has an X and a team name in Listed in Cell F32 then $20 must be added to Cell AL32. If Cell E32 Has an X but no Team name in Cell F32 then nothing should be added to cell AL32 If Cell E32 Has an X and cell E32 is blank But Cell H32 has a team then Cell AL32 should have $20 Example Cell D E F G H I J K L M N O Row 32 DEN ˆš TOR ˆš DET X SOM ˆš VAN X WSH ˆš AL32 Total should be: $40 Cell D E F G H I J K L M N O Row 33 DEN X TOR X SOM ˆš CGY X WSH ˆš AL32 Total should be: $60 Thanks "ShaneDevenshire" wrote: Hi, we need to know more, what do you mean Correct pick = checkmark? What is actually in the cells on a given row? You say add 20 but then you are multiplying by 20, which are you trying to do? Show us a line of sample data. For example, if cell E32 contains X and the next cell contains checkmark, whatever that is, should we add $20. If there are four X's in a row do we add 3*20 or just 20. One problem for example suppose AJ32 has an X, then technically there is only one cell that follows it that can be <"" but your formula checks the entire range from F32:AK32. So if F32 is <"" but does not contain an X but AJ32 contains an X this formula pays $20, is that what you want? -- Thanks, Shane Devenshire "William17" wrote: Hey all need some help correcting this formula =SUMPRODUCT(--(E32:AJ32="x"),--(F32:AK32<""))*20+C32 How it works is follow Correct pick =checkmark Incorrect pick = X If X is followed by anther selection then multiple it by $20 The problem Im having is if the result is X and I skip one cell its not adding the $20 Even if I skip one cell it should add the $20 if another pick is adding along this cell area. Thanks ahead |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Presumably you don't need the double unary minus, as the multiplication will
do the trick. -- David Biddulph "ShaneDevenshire" wrote in message ... Hi, I lost this email notification, sorry. Here are some ideas, not a solution because I couldn't make anything out of the 4th conditions if E32 is blank and E32 has an X then??? Further what has H32 got to do with anything? Further more you seem to be saying if E32 has a X and there is a team name before or after it then $20 - (Rule 1 and 2). If this is really the case you may want to consider redesigning you spreadsheet, because it doesn't lend itself to the formula work. Anyway from the data you provided it looks like if there is an X then $20 in which case the formula would be =SUMPRODUCT(--(A2:K2="X")*20) Adjusted for the appropriate range. -- Thanks, Shane Devenshire "William17" wrote: The formula is listed in cell AL32 The Cell Starts from D32 and ends at AK32 Cell D32 has a team name if correctly picked Cell E32 will have a Checkmark If not Correctly pick E32 will have a X, If Cell E32 has an X and a team name in Listed in Cell F32 then $20 must be added to Cell AL32. If Cell E32 Has an X but no Team name in Cell F32 then nothing should be added to cell AL32 If Cell E32 Has an X and cell E32 is blank But Cell H32 has a team then Cell AL32 should have $20 Example Cell D E F G H I J K L M N O Row 32 DEN ? TOR ? DET X SOM ? VAN X WSH ? AL32 Total should be: $40 Cell D E F G H I J K L M N O Row 33 DEN X TOR X SOM ? CGY X WSH ? AL32 Total should be: $60 Thanks "ShaneDevenshire" wrote: Hi, we need to know more, what do you mean Correct pick = checkmark? What is actually in the cells on a given row? You say add 20 but then you are multiplying by 20, which are you trying to do? Show us a line of sample data. For example, if cell E32 contains X and the next cell contains checkmark, whatever that is, should we add $20. If there are four X's in a row do we add 3*20 or just 20. One problem for example suppose AJ32 has an X, then technically there is only one cell that follows it that can be <"" but your formula checks the entire range from F32:AK32. So if F32 is <"" but does not contain an X but AJ32 contains an X this formula pays $20, is that what you want? -- Thanks, Shane Devenshire "William17" wrote: Hey all need some help correcting this formula =SUMPRODUCT(--(E32:AJ32="x"),--(F32:AK32<""))*20+C32 How it works is follow Correct pick =checkmark Incorrect pick = X If X is followed by anther selection then multiple it by $20 The problem I'm having is if the result is X and I skip one cell its not adding the $20 Even if I skip one cell it should add the $20 if another pick is adding along this cell area. Thanks ahead |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Help with SUMPRODUCT please : ) | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Help with SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumproduct? | Excel Worksheet Functions |