USING OR

[Guest]

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

 

[Biff]

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(R3,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

 

[Guest]

=SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(P3,Overall!B8:B1030))))+=SUMPRODUCT(--(TEXT(Overall!N8:N1030,"mmm/yyyy")=TEXT(J1,"mmm/yyyy")),--(ISNUMBER(SEARCH(R3,Overall!B8:B1030))))

HTH

 

[Biff]

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

 

[Guest]

Thanks. The first formula was the correct one with the "No Possibility."

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Thanks. The first formula was the correct one with the "No Possibility."