Counting Unique Variables WITH Exceptions

I'm trying to write a function that counts the number of unique variables there are in a column including spaces. My current equation is as follows..

=SUM(IF(FREQUENCY(IF(LEN('ESX Hosts'!H:H)>0,MATCH('ESX Hosts'!H:H,'ESX Hosts'!H:H,0),""), IF(LEN('ESX Hosts'!H:H:'ESX Hosts'!H:H)>0,MATCH('ESX Hosts'!H:H,'ESX Hosts'!H:H,0),""))>0,1))

I need it to only count the number of unique entries if it matches the B column's text in the ESX Hosts sheet. So I think I need to create another IF function. I need to read ('ESX Hosts'! B:B. "Blah blah").

I just don't know how to integrate that with the other function. Help is much appreciated!!

Untitled.png

The function would count how many times a variable occurs only once - even if repeated while also not counting blanks. It also counts only for when text in the color's column says the text for the actual color. The same function would be used for each different color.

Originally Posted by oeldere

I try again post an excel file, without confidentional information, also add the desired (expected) result.

Here is a very basic sample excel file. My equation works in counting ALL unique values but I only need it to count when a column includes a certain text.

Example.xlsx

Still haven't been able to get the solution. I attached a more in depth demo of the sheet I'm working with.My comment box in the REPORT tab mentions what I need to do.

Thanks again to whomever can help. This is giving me a serious headache.

You should avoid using entire columns as range references in array formulas.

The reason I'm using whole columns is because this sheet is being continually updated with added rows daily. I could just throw in a high enough number, though.

pjd2011 Demo.xls

Originally Posted by Tony Valko

Sorry, your file is too big. I won't download files >50kb.

Ok, I removed a lot of the information to shrink it down to just the bare basics.

pjd2011 Demo.xlsx

Originally Posted by Tony Valko

Sorry, but your data doesn't seem to match your description and I don't understand your comments.

On the Report sheet you have "categories" of Blue, Red, Green, and Yellow but I don't see any of those categories on the Main sheet.

Sorry about that. I just came up with those categories as dummy variables. Didn't think that one through. I think I used the numbers 1-7 as variables instead of the colors.

Originally Posted by newdoverman

Part of your conditions are contradictory. "The function would count how many times a variable occurs only once - even if repeated" How can something only occur once and be repeated?]

So if the variable D121 showed up 5 different times it would be counted only one time is what I was trying to convey. The equation I need would also add the function of only counting data that included a certain text in a column.

I, again, updated the Excel sheet to the accurate information and a more detailed explanation of what I am looking for. Please continue to try as my brain is fried trying!! Thank you!

pjd2011 Demo.xlsx

Originally Posted by newdoverman

It would have been nice to know the real problem instead of being presented with a problem that changes into something completely different.

Good luck

I feel as though I have been clear in showing what the problem is. I had to go through and delete/alter much of the information I'm working on as it is not public. I apologize that my information is not up to par, I figured this community would be understanding.