Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

BeeBear2

Technical User
Sep 15, 2014
39
AU
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
 
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
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Oops!! my bad! I am used to working in Crystal so much I posted in the wrong spot - sorry guys!!

Merry Christmas
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top