Sumproduct using contains rather than equals

[andibevan]

Does anybody know of a way of using the Sumproduct formula to coun
cells that contain text. I have tried:-

=sumproduct((a5:a508)="RSK")*(E8:E508)="*ACT*")) but it does not work
it returns 0 when the answer is 8.

This should count the number of RSK's in column A where ACT i
contained somewhere within the corresponding cell in column E

Has anybody got any ideas?

Thanks

And

 

[Domenic]

Hi Andy,

Try the following formula:

=SUMPRODUCT((A5:A508="RSK")*(ISNUMBER(FIND("ACT",E5:E508))))

Hope this helps

 

[Guest]

Hi

Try something like:
=SUMPRODUCT((A5:A508="RSK")*--(ISNUMBER(FIND("ACT",E5:E508))))

 

[GerryK]

=SUMPRODUCT(--(A5:A508="RSK"),--(E8:B508="ACT"))

 

[Harlan Grove]

=sumproduct((a5:a508)="RSK")*(E8:E508)="*ACT*")) but it does not work -
it returns 0 when the answer is 8.

You have too many right parentheses. Also your cols A and E ranges have
different numbers of rows, which should throw a #NUM! error.

Better to copy from the Formula bar and paste into newsgroup postings.

This should count the number of RSK's in column A where ACT is
contained somewhere within the corresponding cell in column E

Just another alternative.

=SUMPRODUCT((A5:A508="RSK")*(SUBSTITUTE(E5:E508,"ACT","")<>E5:E508))

This may be less recalc efficient than ISNUMBER(FIND(...)), but it uses only
one function call, and that's sometimes important in very complex formulas.

 

[Myrna Larson]

Others have proposed solutions, but I just wanted to point out that the
asterisk(s) don't act as wild cards in an array formula. The cell would have
to contain the exact text *ACT* for your formula to call it a match.