Sumproduct formula

[donny]

The following formula is returning a VALUE#! error. I want
to count how many lines in column J has the alphabet D and
column O have the word no. Any help will be greatly
appreciated, thanks.


=SUMPRODUCT('Shipping Report'!J2:J1000="*D*")*('Shipping
Report'!O2:O1000="No")

 

[Dan E]

Donny

=SUMPRODUCT((ISERR(SEARCH("D",'Shipping Report'!J2:J1000))=FALSE)*('ShippingReport'!O2:O1000="No"))

Dan E

 

[Peo Sjoblom]

=SUMPRODUCT(ISNUMBER(FIND("D",J2:J1000))*(O2:O1000="NO"))

insert 'Shipping Report'! in the formula

if there are more characters in O than "NO" use the same technique on that
column

 

[Harlan Grove]

I still get a value#! error entered the following formula:
=SUMPRODUCT(ISNUMBER(FIND("D",'Shipping Report'!J2:J1000))*
('Shipping Report'!O2:O1000="NO"))

...

The formula above would only return a #VALUE! error if cells in either 'Shipping
Report'!J2:J1000 or 'Shipping Report'!O2:O1000 evaluate to error values. What do
you get from the formula

=SUMPRODUCT(--ISERROR('Shipping Report'!J2:J1000&'Shipping Report'!O2:O1000))

? If it gives a nonzero result, you have error values in these ranges.