|
USING OR
I am using the below formula. What I want to do is in the last part of this
formula add a "or." I want it to not only look at P3 but R3 as well. =SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,Overall!B8:B1030)))) |
USING OR
Hi!
Note: if there are empty cells in the N8:N1030 range and your search criteria cell were also empty you could get false positives using your current formula. I'll assume that's not a possibility. If there is NO POSSIBILITY that BOTH P3 and R3 will be in any cell at the same time: =SUMPRODUCT(--(TEXT(N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,B8:B1030)))+(ISNUMBER(SEARCH(R 3,B8:B1030)))) If there is A POSSIBILITY that BOTH P3 and R3 will be in any cell at the same time: =SUMPRODUCT(--(TEXT(N8:A1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--((ISNUMBER(SEARCH(P3,B8:B1030)))+(ISNUMBER(SEARCH( R3,B8:B1030)))0)) Biff "roy.okinawa" wrote in message ... I am using the below formula. What I want to do is in the last part of this formula add a "or." I want it to not only look at P3 but R3 as well. =SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,Overall!B8:B1030)))) |
USING OR
=SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,Overall!B8:B1030))))+=SUMPRODU CT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(R3,Overall!B8:B1030))))
HTH "roy.okinawa" wrote: I am using the below formula. What I want to do is in the last part of this formula add a "or." I want it to not only look at P3 but R3 as well. =SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,Overall!B8:B1030)))) |
USING OR
Ooops!
correction: Note: if there are empty cells in the N8:N1030 range and your search criteria cell were also empty Should be: Note: if there are empty cells in the N8:N1030 range and your date criteria cell were also empty Empty cells evaluated by either of these: TEXT(N8:N1030,"mmm/yyyy") TEXT(J1,"mmm/yyyy") Would return: Jan/1900 Biff "Biff" wrote in message ... Hi! Note: if there are empty cells in the N8:N1030 range and your search criteria cell were also empty you could get false positives using your current formula. I'll assume that's not a possibility. If there is NO POSSIBILITY that BOTH P3 and R3 will be in any cell at the same time: =SUMPRODUCT(--(TEXT(N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,B8:B1030)))+(ISNUMBER(SEARCH(R 3,B8:B1030)))) If there is A POSSIBILITY that BOTH P3 and R3 will be in any cell at the same time: =SUMPRODUCT(--(TEXT(N8:A1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--((ISNUMBER(SEARCH(P3,B8:B1030)))+(ISNUMBER(SEARCH( R3,B8:B1030)))0)) Biff "roy.okinawa" wrote in message ... I am using the below formula. What I want to do is in the last part of this formula add a "or." I want it to not only look at P3 but R3 as well. =SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,Overall!B8:B1030)))) |
USING OR
Thanks. The first formula was the correct one with the "No Possibility."
"Biff" wrote: Hi! Note: if there are empty cells in the N8:N1030 range and your search criteria cell were also empty you could get false positives using your current formula. I'll assume that's not a possibility. If there is NO POSSIBILITY that BOTH P3 and R3 will be in any cell at the same time: =SUMPRODUCT(--(TEXT(N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,B8:B1030)))+(ISNUMBER(SEARCH(R 3,B8:B1030)))) If there is A POSSIBILITY that BOTH P3 and R3 will be in any cell at the same time: =SUMPRODUCT(--(TEXT(N8:A1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--((ISNUMBER(SEARCH(P3,B8:B1030)))+(ISNUMBER(SEARCH( R3,B8:B1030)))0)) Biff "roy.okinawa" wrote in message ... I am using the below formula. What I want to do is in the last part of this formula add a "or." I want it to not only look at P3 but R3 as well. =SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,Overall!B8:B1030)))) |
USING OR
You're welcome. Thanks for the feedback!
Biff "roy.okinawa" wrote in message ... Thanks. The first formula was the correct one with the "No Possibility." "Biff" wrote: Hi! Note: if there are empty cells in the N8:N1030 range and your search criteria cell were also empty you could get false positives using your current formula. I'll assume that's not a possibility. If there is NO POSSIBILITY that BOTH P3 and R3 will be in any cell at the same time: =SUMPRODUCT(--(TEXT(N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,B8:B1030)))+(ISNUMBER(SEARCH(R 3,B8:B1030)))) If there is A POSSIBILITY that BOTH P3 and R3 will be in any cell at the same time: =SUMPRODUCT(--(TEXT(N8:A1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--((ISNUMBER(SEARCH(P3,B8:B1030)))+(ISNUMBER(SEARCH( R3,B8:B1030)))0)) Biff "roy.okinawa" wrote in message ... I am using the below formula. What I want to do is in the last part of this formula add a "or." I want it to not only look at P3 but R3 as well. =SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,Overall!B8:B1030)))) |
| All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com