SUMPRODUCT with conditional OR
 

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.

SUMPRODUCT with conditional OR
 

Hi,

Try this

=IF(AK1014="","",SUMPRODUCT((OR(G3:G10024,H3:H100 24))*(AK3:AK1002)))

--
Mike

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


"Struggling in Sheffield" wrote:

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.

SUMPRODUCT with conditional OR
 

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))

--
Best Regards,

Luke M
"Struggling in Sheffield"
wrote in message ...
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.

SUMPRODUCT with conditional OR
 

"Struggling in Sheffield" wrote:
=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 -----

"Struggling in Sheffield" wrote:
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.

SUMPRODUCT with conditional OR
 

"Mike H" wrote:
Try this
=IF(AK1014="","",
SUMPRODUCT((OR(G3:G10024,H3:H10024))
*(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 -----

"Mike H" wrote:

Hi,

Try this

=IF(AK1014="","",SUMPRODUCT((OR(G3:G10024,H3:H100 24))*(AK3:AK1002)))

--
Mike

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


"Struggling in Sheffield" wrote:

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.

SUMPRODUCT with conditional OR
 

I wrote:
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.

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


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

"Joe User" wrote:
"Struggling in Sheffield" wrote:
=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 -----

"Struggling in Sheffield" wrote:
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.

SUMPRODUCT with conditional OR
 

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" wrote:

Hi,

Try this

=IF(AK1014="","",SUMPRODUCT((OR(G3:G10024,H3:H100 24))*(AK3:AK1002)))

--
Mike

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


"Struggling in Sheffield" wrote:

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.

SUMPRODUCT with conditional OR
 

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.


"Joe User" wrote:

"Mike H" wrote:
Try this
=IF(AK1014="","",
SUMPRODUCT((OR(G3:G10024,H3:H10024))
*(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 -----

"Mike H" wrote:

Hi,

Try this

=IF(AK1014="","",SUMPRODUCT((OR(G3:G10024,H3:H100 24))*(AK3:AK1002)))

--
Mike

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


"Struggling in Sheffield" wrote:

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.

SUMPRODUCT with conditional OR
 

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

"Luke M" wrote:

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))

--
Best Regards,

Luke M
"Struggling in Sheffield"
wrote in message ...
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.


.