Sumproduct "not equal"

[Terry]

The sumproduct formula does not provide the correct answer.

J=I&C
H=Blank
Search B3:B3270 for "Phase I"
Search B3:B3270 for "Formual"
If M3:M3270 = "D" do not count

Here is my formula
=SUMPRODUCT((J3:J3270="I&C")*(H3:H3270="")*((ISNUMBER(SEARCH("Phase
I",B3:B3270))+(ISNUMBER(SEARCH("Formal",B3:B3270)))*(M3:M3270<>"D"))))

 

[Jacob Skaria]

Try the below..."Formual" or "Formal" ??

=SUMPRODUCT((J3:J3270="I&C")*(H3:H3270="")
ISNUMBER(MATCH(B3:B3270,{"Phase I","Formal"},0))*(M3:M3270<>"D"))

If this post helps click Yes

 

[T. Valko]

Try this (all on one line):

=SUMPRODUCT(--(J3:J3270="I&C"),--(H3:H3270=""),
(ISNUMBER(SEARCH("Phase I",B3:B3270))+
ISNUMBER(SEARCH("Formal",B3:B3270))),
--(M3:M3270<>"D"))

 

[Jacob Skaria]

Correction..

=SUMPRODUCT((J3:J3270="I&C")*(H3:H3270="")*
ISNUMBER(MATCH(B3:B3270,{"Phase I","Formal"},0))*(M3:M3270<>"D"))

If this post helps click Yes

 

[David Biddulph]

"does not provide the correct answer" is not a very explicit description of
the problem.
What answer did you get? What answer did you expect?
I assume that your "Formual" is just a typo?
Are there any cells in column B which contain both "Phase I" and "Formal"?
If so you will have counted them twice.

I think also that you've got confused with your parentheses. Look at them
carefully and see where they match. I think that you may have intended
=SUMPRODUCT((J3:J3270="I&C")*(H3:H3270="")*(ISNUMBER(SEARCH("Phase
I",B3:B3270))+ISNUMBER(SEARCH("Formal",B3:B3270)))*(M3:M3270<>"D"))

To solve the double counting of column B, try changing it to
=SUMPRODUCT((J3:J3270="I&C")*(H3:H3270="")*((ISNUMBER(SEARCH("Phase
I",B3:B3270))+ISNUMBER(SEARCH("Formal",B3:B3270)))>0)*(M3:M3270<>"D"))

If you've still got problems, are the cells in column H really blank, or do
they contain spaces or other non-printing characters?