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="")*((ISNUM BER(SEARCH("Phase
I",B3:B3270))+(ISNUMBER(SEARCH("Formal",B3:B3270)) )*(M3:M3270<"D"))))

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
---------------
Jacob Skaria


"Terry" wrote:

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="")*((ISNUM BER(SEARCH("Phase
I",B3:B3270))+(ISNUMBER(SEARCH("Formal",B3:B3270)) )*(M3:M3270<"D"))))

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

--
Biff
Microsoft Excel MVP


"Terry" wrote in message
...
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="")*((ISNUM BER(SEARCH("Phase
I",B3:B3270))+(ISNUMBER(SEARCH("Formal",B3:B3270)) )*(M3:M3270<"D"))))

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
---------------
Jacob Skaria


"Terry" wrote:

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="")*((ISNUM BER(SEARCH("Phase
I",B3:B3270))+(ISNUMBER(SEARCH("Formal",B3:B3270)) )*(M3:M3270<"D"))))

"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="")*(ISNUMB ER(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="")*((ISNUM BER(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?
--
David Biddulph


"Terry" wrote in message
...
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="")*((ISNUM BER(SEARCH("Phase
I",B3:B3270))+(ISNUMBER(SEARCH("Formal",B3:B3270)) )*(M3:M3270<"D"))))