Excel formula problem - SEARCH/FIND

[BeeBear2]

Hi

Using Excel 14 - MS Office 2010

=SUMPRODUCT(--($G:$G=$E3),--($L:$L="WED"))

This formula above, is counting the number of times the contents of Cell E3 (a name/phone number combo) occur in the cells of Column G, where the equivalent row in Column L contains "WED"
Reference Cell E3 contains the persons name.
My problem is, the Column G data cells, may contain MORE info than is in Cell E3 (EG Name/phone number combo PLUS a comment), making it not match.
At the moment it only includes where the cell in column G totally matches E3. I need it to count these other cells aswell.

Any help would be appreciated.

PS - I've inherited this formula so don't fully understand the double-negative impact.

Thanks

 

[SkipVought]

Hi,

I'd write the formula as
[tt]
=SUMPRODUCT((IFERROR(FIND(E3,$G:$G),FALSE)>0)*($L:$L="WED"))
[/tt]

The double unary (--) coerces the calculation of TRUEs & FALSEs as 1s & 0s, but it's not necessary in this case.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[BeeBear2]

Thanks Skip,

But it doesnt give me the correct answer - I've done my manual count and I get 2, but for your formula I get 47 (and the same answer for every different name (eg E3, E4, E5 - all cells with different names in)

Can you explain what your formula does for me?

I might be able to tweak it a little to get what I need

 

[SkipVought]

I never use entire column references in SUMPRODUCT like G:G. As I recall, this is a limitation of SUMPRODUCT. You must use a range LESS THAN an entire column. I ALWAYS work within a table, most often a Structured Table.

Therefore, 1) [highlight #FCAF3E]limit the range of rows in G & L[/highlight], 2) [highlight #FCE94F]change FALSE to 0[/highlight] and 3) enter the formula as an Array Formula (ctrl+shift+enter), due to the FIND() function.
[tt]
=SUMPRODUCT((IFERROR(FIND(E3,[highlight #FCAF3E]G2:G10[/highlight]),[highlight #FCE94F]0[/highlight])>0)*([highlight #FCAF3E]L2:L10[/highlight]="WED"))
[/tt]

My table range was 10 rows. Make yours as appropriate.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}