|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 21st, 2010, 10:27 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT with criteria including OR function
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$ 1002J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0," ",SUMPRODUCT(($AJ$3:$AJ$1002=I1320)*($AJ$3:$AJ$10 02J1320)*($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. 
|
|
#2
April 21st, 2010, 11:36 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT with criteria including OR function
"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$1002J1320) *(($G$3:$G$10023)+($H$3:$H$10023)0))=0, "", SUMPRODUCT(($AJ$3:$AJ$1002=I1320) *($AJ$3:$AJ$1002J1320) *(($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$1002J1320) *(($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$ 1002J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0," ",SUMPRODUCT(($AJ$3:$AJ$1002=I1320)*($AJ$3:$AJ$10 02J1320)*($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.
|
|
#3
April 21st, 2010, 11:37 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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$1002J1320) *(($G$3:$G$10023)+($H$3:$H$10023)0))=0, "", SUMPRODUCT(($AJ$3:$AJ$1002=I1320) *($AJ$3:$AJ$1002J1320) *(($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$1002J1320) *(($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$ 1002J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0," ",SUMPRODUCT(($AJ$3:$AJ$1002=I1320)*($AJ$3:$AJ$10 02J1320)*($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.
|
|
#4
April 21st, 2010, 11:41 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT with criteria including OR function
I wrote:
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 ----- "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$1002J1320) *(($G$3:$G$10023)+($H$3:$H$10023)0))=0, "", SUMPRODUCT(($AJ$3:$AJ$1002=I1320) *($AJ$3:$AJ$1002J1320) *(($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$1002J1320) *(($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$ 1002J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0," ",SUMPRODUCT(($AJ$3:$AJ$1002=I1320)*($AJ$3:$AJ$10 02J1320)*($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.
|
|
#5
April 22nd, 2010, 10:23 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT with criteria including OR function
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. "Joe User" wrote: I wrote: 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 ----- "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$1002J1320) *(($G$3:$G$10023)+($H$3:$H$10023)0))=0, "", SUMPRODUCT(($AJ$3:$AJ$1002=I1320) *($AJ$3:$AJ$1002J1320) *(($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$1002J1320) *(($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$ 1002J1320)*($G$3:$G$1002=3)*($H$3:$H$1002=3))=0," ",SUMPRODUCT(($AJ$3:$AJ$1002=I1320)*($AJ$3:$AJ$10 02J1320)*($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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
