Sumproduct with Unique Instances

[Chip_Excel]

Hi Everyone,
I am having problems getting an accurate number of unique items in an array based on a criteria.

I have a range J2:J1788 and within that range are 95 unique instances (hand counted) based off of two criteria: X in column AE and Y in column G.

Here is my formula:

=SUMPRODUCT(IF(Sheet6!$AE$2:$AE$1788=X,1,0)*IF(Sheet6!$G$2:$G$1788=Y,1,0)*1/COUNTIF(Sheet6!$J$2:$J$1788,Sheet6!$J$2:$J$1788))

The formula returns: 70.6549784

My guess that it is parsing up the unique instances to decimals, i.e. if there are 4 instances of a unique value the formula counts it as .25 instead of 1.

Any ideas how I can get the formulas to count it as one? Can I place a ROUNDUP function in there somewhere?

Best,
Chip

 

[barry houdini]

Hello Chip,

I don't think that approach will work - Do you have excel 2007 or later, in which case try

=SUMPRODUCT((sheet6!$AE$2:$AE$1788="X")*(sheet6!$G$2:$G$1788="Y")/COUNTIFS(sheet6!$AE$2:$AE$1788,sheet6!$AE$2:$AE$1788&"",sheet6!$G$2:$G$1788,sheet6!$G$2:$G$1788&"",sheet6!$J$2:$J$1788,sheet6!$J$2:$J$1788&""))

I put "X" and "Y" in quotes I assume they aren't named ranges.....?

 

[Chip_Excel]

AWWW YEAH. Worked like a charm! Thank you SOOOOO much. This has been driving me nuts for so long.