SUMPRODUCT

[William17]

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

 

[ShaneDevenshire]

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?

 

[William17]

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]

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.

 

[ShaneDevenshire]

I thought I would add this modification in case it gave you any ideas:

=SUMPRODUCT(--(B2:K2="√"),--(A2:J2<>""))*20

 

[David Biddulph]

Presumably you don't need the double unary minus, as the multiplication will
do the trick.