Sumproduct Formula Help
 

=SUMPRODUCT((ISNUMBER(FIND({"FLH","FLT"},L2:L100)) )*(M2:M100="N"),P2:P100


The two strings are creating an error? Formula works fine with one text
string. Anyway to resolve?

harley

Sumproduct Formula Help
 

=SUMPRODUCT(ISNUMBER(FIND({"FLT","FLH"},L2:L100))* (M2:M100="N")*(P2:P100))



"Harley" wrote:

=SUMPRODUCT((ISNUMBER(FIND({"FLH","FLT"},L2:L100)) )*(M2:M100="N"),P2:P100


The two strings are creating an error? Formula works fine with one text
string. Anyway to resolve?

harley

Sumproduct Formula Help
 

I take in column L could contain FLT and/or FLH within other text?

Note that this formula could "double-count", e.g. if L3 = "FLT xxxFLH",
M3="N", P3=10 then the 10 would be added to the total twice.

"JMB" wrote:

=SUMPRODUCT(ISNUMBER(FIND({"FLT","FLH"},L2:L100))* (M2:M100="N")*(P2:P100))



"Harley" wrote:

=SUMPRODUCT((ISNUMBER(FIND({"FLH","FLT"},L2:L100)) )*(M2:M100="N"),P2:P100


The two strings are creating an error? Formula works fine with one text
string. Anyway to resolve?

harley

Sumproduct Formula Help
 

Yes, there is a potential for double counting. And, if case sensitivity is
an issue, he may need to use SEARCH.

=SUMPRODUCT((ISNUMBER(FIND("FLH",L2:L100))+ISNUMBE R(FIND("FLT",L2:L100))0)*(M2:M100="N")*(P2:P100))


"daddylonglegs" wrote:

I take in column L could contain FLT and/or FLH within other text?

Note that this formula could "double-count", e.g. if L3 = "FLT xxxFLH",
M3="N", P3=10 then the 10 would be added to the total twice.

"JMB" wrote:

=SUMPRODUCT(ISNUMBER(FIND({"FLT","FLH"},L2:L100))* (M2:M100="N")*(P2:P100))



"Harley" wrote:

=SUMPRODUCT((ISNUMBER(FIND({"FLH","FLT"},L2:L100)) )*(M2:M100="N"),P2:P100


The two strings are creating an error? Formula works fine with one text
string. Anyway to resolve?

harley