Count unique on 1 column include rows per another column using SUMPRODUCT ...

[ReggieMack]

I'm trying to count unique occurrances of items in a particular column of a table using SUMPRODUCT. The formula that is working for me is SUMPRODUCT((IF(FREQUENCY(B1:B11,B1:B11)>0,1,0))). This works just fine!

My intent is to add criteria, incrementally, building the formula to inlude/exclude rows based on other columns in the table. My 1st attempt was to include rows marked to include with a "Yes" in an "Include" column. For example:

"A" "B"

# Item Include # Item Include
1 100 Yes 1 100 Yes
2 103 Yes 2 103 Yes
3 100 Yes 3 100 Yes
4 103 Yes 4 103 No
5 109 Yes 5 109 No
6 104 Yes 6 104 No
7 102 Yes 7 102 Yes
8 111 Yes 8 111 Yes
9 100 Yes 9 100 Yes

So, in this example with the 2 tables, Table "A" should produce a count of 6 unique items and Tabe "B" should produce a count of 4 unique items based on the "Include" column and either excluding "Items" rows if "Include" is "No" or including "Items" rows if "Include" is "Yes" into the count uniques on the "Item" column using the SUMPRODUCT formulation above as the basis and adding additional criteria to include/exclude. I've tried several permutations including the asterisk, the double unary ",--", etc. I've even tried:

=SUMPRODUCT((IF(FREQUENCY(IF(IncludeRng="Yes",ItemRng,""),ItemRng)>0,1))).

I was quite certain that this would work. But, it didn't. Apparently, I don't understand the SUMPRODUCT function as well as I had thought!!! Does anyone have any suggestions? For the sake of my education on this function, I'd like to stay with utilizing SUMPRODUCT as the basis for any solutions. Thanks all!

 

[ReggieMack]

This is my first post so although I put a significant amount of space between the 2 example tables I didn't realize that the spaces would be taken out. So, I'll repost the tables here as:

Table "A"

# Itm Inc
- --- ---
1 100 Yes
2 103 Yes
3 100 Yes
4 103 Yes
5 109 Yes
6 104 Yes
7 102 Yes
8 111 Yes
9 100 Yes


Table "B"

# Itm Inc
- --- ---
1 100 Yes
2 103 Yes
3 100 Yes
4 103 No
5 109 No
6 104 No
7 102 Yes
8 111 Yes
9 100 Yes

 

[Aladin Akyurek]

Try...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(
   IF(ItemRng<>"",
   IF(IncludeRng="Yes",MATCH("~"&ItemRng,ItemRng&"",0))),
    ROW(ItemRng)-ROW(INDEX(ItemRng,1,1))+1),1))

 

[ReggieMack]

Yes, this does work and thank-you!!!

Yet, the idea hre is to produce a solution using the SUMPRODUCT function. Any suggestions there?

Thanks, again!

 

[Aladin Akyurek]

Yes, this does work and thank-you!!!

You are welcome.

Yet, the idea hre is to produce a solution using the SUMPRODUCT function.

Any particular reason for that?

Any suggestions there?...

Maybe this:

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[ReggieMack]

Yes, I'm trying to better understand the sumproduct function. The attempts That I made to add criteria to the sumproduct function that counted unique entries in a table column were unsuccessful, although I was fairly certain that at least 2 of my attempts would work or at least come close to my expectation. I was quite surprised to see how far off that I was. Hence, I'm determined to ascertain a full understanding of this function. Does anyone know of any walk-through or step-by-step doc on SUMPRODUCT?

 

[Aladin Akyurek]

Yes, I'm trying to better understand the sumproduct function. The attempts That I made to add criteria to the sumproduct function that counted unique entries in a table column were unsuccessful, although I was fairly certain that at least 2 of my attempts would work or at least come close to my expectation. I was quite surprised to see how far off that I was. Hence, I'm determined to ascertain a full understanding of this function. Does anyone know of any walk-through or step-by-step doc on SUMPRODUCT?

http://www.mrexcel.com/forum/showthread.php?t=56778

http://www.mrexcel.com/forum/showthread.php?t=202204

http://www.mrexcel.com/forum/showthread.php?t=70547

http://www.mrexcel.com/forum/showthread.php?t=128907

A note. SumProduct is constructed as a function that processes arrays. Embedding a IF into it would require control+shift+enter for IF itself requires that signal. So, whenever you need an IF which must yield an array object, it is more convenient and better to switch to SUM...

 

[ReggieMack]

I thank you once, again! Wish me luck!

Ciao!