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
|