SUMPRODUCT with criteria including OR function

[Struggling in Sheffield]

Hi chaps,
Excel 2003
I'm using the following formula to sum the number of times that the #3
appears on the same Row in both Columns G3:G1002 & H3:H1002, where the value
in Column AJ3:AJ1002 is between 2 other distinct values (located in a
seperate table, cells I1320 & J1320).

=IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0,"",SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)*($AJ$3:$AJ$1002<J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3)))

All works fine, but now instead of the #3 I need to introduce an OR function
so that if either of (or both) the values in G3:G1002 & H3:H1002 exceed 3
(are 4 or greater), then it sums the number or times this occurs.

Need to stress that if either or both of the two values exceed 3, then it
sums the number of occurances.

Many thanks for looking, hope you can help.
Steve.

 

[Joe User]

but now instead of the #3 I need to introduce an
OR function so that if either of (or both) the values
in G3:G1002 & H3:H1002 exceed 3 (are 4 or
greater), then it sums the number or times this occurs.

The following __counts__ the number of such occurrances in conjunction with
the additional criteria, which is what I think you mean by "introduce an OR
function".

=IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))=0,
"",
SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0)))

By the way, here is a simpler alternative to consider:

=SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0)))

with the Custom format: General;-General;""


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

 

[Joe User]

The following __counts__ the number of such occurrances in conjunction with
the additional criteria, which is what I think you mean by "introduce an OR
function".

=IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))=0,
"",
SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0)))

By the way, here is a simpler alternative to consider:

=SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0)))

with the Custom format: General;-General;""


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

 

[Joe User]

By the way, here is a simpler alternative to consider: [....]
with the Custom format: General;-General;""

I meant to add.... With this approach, the value in the cell will actually
be zero, not the null string (""). So you would test for A1=0 instead of
A1="" wherever applicable. That should not be a problem since your current
formula will not return zero. In fact, it should be a plus since you can use
A1*3 directly (e.g.) instead of some work-around like N(A1)*3.

PS: Sorry about the reply with no additional comment. Clicked on the wrong
button inadvertently.


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

 

[Struggling in Sheffield]

Thanks for that Joe, was struggling to bottom that one out.
Have used the less simple one to for now as I understand it and it works!
Forward march again 'til I hit the next brick wall.
Cheers.

By the way, here is a simpler alternative to consider: [....]
with the Custom format: General;-General;""

I meant to add.... With this approach, the value in the cell will actually
be zero, not the null string (""). So you would test for A1=0 instead of
A1="" wherever applicable. That should not be a problem since your current
formula will not return zero. In fact, it should be a plus since you can use
A1*3 directly (e.g.) instead of some work-around like N(A1)*3.

PS: Sorry about the reply with no additional comment. Clicked on the wrong
button inadvertently.


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

The following __counts__ the number of such occurrances in conjunction with
the additional criteria, which is what I think you mean by "introduce an OR
function".

=IF(SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0))=0,
"",
SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0)))

By the way, here is a simpler alternative to consider:

=SUMPRODUCT(($AJ$3:$AJ$1002>=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$1002>3)+($H$3:$H$1002>3)>0)))

with the Custom format: General;-General;""


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