SUMPRODUCT with conditional OR

[Struggling in Sheffield]

Hi chaps,
Am using the following formula to check two columns of numbers (G3-G1002 &
H3-H1002). If the value in either column is 4 or greater, I'm summing a
corresponding value in column AK (AK3-AK1002):

=IF(AK1014="","",SUMPRODUCT(--(($G$3:$G$1002>=4)+($H$3:$H$1002>=4)),--($G$3:$G$1002<>""),--($H$3:$H$1002<>""),AK3:AK1002))

This works fine except when both values are 4 or greater, as it then sums
the value in column AK twice.

Would appreciate any help to tweak the formula into submission.
Thanks for looking.

 

[Mike H]

Hi,

Try this

=IF(AK1014="","",SUMPRODUCT((OR(G3:G1002>4,H3:H1002>4))*(AK3:AK1002)))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Luke M]

We can add another boolean check like so:

=IF(AK1014="","",SUMPRODUCT(--(($G$3:$G$1002>=4)+($H$3:$H$1002>=4)>0),--($G$3:$G$1002<>""),--($H$3:$H$1002<>""),AK3:AK1002))

 

[Joe User]

=IF(AK1014="","",
SUMPRODUCT(--(($G$3:$G$1002>=4)+
($H$3:$H$1002>=4)), --($G$3:$G$1002<>""),
--($H$3:$H$1002<>""),AK3:AK1002))

This works fine except when both values are 4 or
greater, as it then sums the value in column AK twice.

That is exactly what happens. The problem is that ($G$3...)+($H$3...)
evaluates to 2 when both conditions are true, resulting in multiplying by 2
instead of 1 (TRUE).

Use one of the following, whichever is easier for you to maintain. They are
equivalent. The latter avoids using "--":

=IF(AK1014="","",
SUMPRODUCT(
--(($G$3:$G$1002>=4)+($H$3:$H$1002>=4)<>0),
--($G$3:$G$1002<>""), --($H$3:$H$1002<>""),
AK3:AK1002))

---or---

=IF(AK1014="","",
SUMPRODUCT(
(($G$3:$G$1002>=4)+($H$3:$H$1002>=4)<>0)
*($G$3:$G$1002<>"")*($H$3:$H$1002<>""), AK3:AK1002))


----- original message -----

 

[Joe User]

Try this
=IF(AK1014="","",
SUMPRODUCT((OR(G3:G1002>4,H3:H1002>4))
*(AK3:AK1002)))

Yes, __you__ should try it. I think you will find the following mistakes.

1. You cannot use OR() in this context. The reason is: OR will process the
array arguments, not SUMPRODUCT. Consequently, you will not select from
AK3:AK1002 according to the intended criteria. This why we use
(G3:...)+(H3:...) instead.

2. The tests for G3:G1002<>"" and H3:H1002<>"" are not redundant, as you
assume. True, they are unneeded when any of G3:G1002 or H3:H1002 are truly
empty, since they will be treated as zero in that context. However, if any
cells in those ranges only __appear__ blank, but actually contain null
strings -- such as the result of the formula when AK1014 appears blank -- the
test G3:G1002>=4 returns TRUE, for example.

3. Arguably, ",AK3:AK1002" as the OP wrote is better than "*AK3:AK1002" as
you wrote. See the SUMPRODUCT help page for the cell contents that
SUMPRODUCT ignores.

However, I do agree with the attempt to remove double negations ("--").
See the second formula in my response to the OP.

4. You changed ">=4" to ">4". Perhaps just a typo. Use copy-and-paste to
avoid that in the future.


----- original message -----

 

[Joe User]

That is exactly what happens.

Arrgghh! I meant to write: "That is __not__ exactly what happens".


----- original message -----

 

[Mike H]

Hmmm

That doesn't work
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Yes I just posted a correction, teach me to test before posting. Thanks
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Struggling in Sheffield]

Thanks for that chaps, this one works fine.
Onwards and upwards.