SUMPRODUCT with criteria including OR function
"Joe User" wrote:
"Struggling in Sheffield" wrote:
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$10023)+($H$3:$H$10023)0))=0,
"",
SUMPRODUCT(($AJ$3:$AJ$1002=I1320)
*($AJ$3:$AJ$1002<J1320)
*(($G$3:$G$10023)+($H$3:$H$10023)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$10023)+($H$3:$H$10023)0)))
with the Custom format: General;-General;""
----- original message -----
"Struggling in Sheffield" wrote:
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$10 02<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.
|