Help counting a range of cells that contain the data in a reference cell

[BeeBear2]

Hi :-D

Using Crystal Reports XI

=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

 

[bdreed35]

That looks like an Excel formula. Did you intend to post this in an Excel forum or do you need help converting this over to Crystal Reports?

~Brian

 

[SkipVought]

Yes this appears to be an Excel function.

The double unary (--) which equals 1, coerces the calculation of the array expression, (the TRUEs & FALSEs) into 1s & 0s within the SUMPRODUCT().

The fact that the data came from CR is irrelevant. The fact that you're using Excel to analyze the data dictates that you ought to be posting this question in forum68.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BeeBear2]

Oops!! my bad! I am used to working in Crystal so much I posted in the wrong spot - sorry guys!!

Merry Christmas