COUNTIFS -- Count multiple entries as one

[RodneyC]

I'm using this formula =COUNTIFS(RevenueALL!B:B,Sheet4!C13,RevenueALL!P:P,">0")) and it does indeed do what it's being asked to do. However, what I really need are two additional things. We can tackle them one at a time, but it may be valuable to know the endgame before we start. Here is what I need:


1. The formula needs to count each unique entry one time. If "Vanilla Beans" shows up thirty seven times, it will only count as one.

2. Understand that due to inconsistent data entry, there may be several ways "Vanilla Beans" may have been entered. "VanillaBeans" and "Vanilla_Beans" are two possible examples. All forms of Vanilla Beans should count equally. There are about 75,000 lines in this report and it's likely that other unique entries such as "Vanilla Ice Cream" and all forms it could come as "Vanilla_Ice_Cream" etc., may exist and need to count separately from any form of "Vanilla Beans".

Thanks in advance.

 

[RodneyC]

IBUMPOING with revision

I'm using this formula =COUNTIFS(RevenueALL!$B:$B,Sheet4!$C12, RevenueALL!$H:$H,$C$2,RevenueALL!$P:$P,">0") and it does indeed do what it's being asked to do.

I need some help with evolving it. In addition to what it's looking at, I need it to look in RevenueALL!$C:$C and report how many unique entries there are that meet all other criteria in the formula.

thanks

 

[Randall]

This may not be the exact solution, but to start, are there a finite amount of variable names each item could be called? If so, I would consider writing a VBA script to do a "Find and Replace" for all of your variable names for your items so that you can standardize them. This would be a tad tedious at first depending on how many items you have, but would make the counting itself easier and you would only have to add to it if a new item is ever added.

 

[RodneyC]

This may not be the exact solution, but to start, are there a finite amount of variable names each item could be called? If so, I would consider writing a VBA script to do a "Find and Replace" for all of your variable names for your items so that you can standardize them. This would be a tad tedious at first depending on how many items you have, but would make the counting itself easier and you would only have to add to it if a new item is ever added.

There are about 70,000 lines with hundreds of new ones being added each week and hundreds dropping off.

 

[Randall]

There are about 70,000 lines with hundreds of new ones being added each week and hundreds dropping off.

With that the problem still persists where you'll need to find some unique variable to each type of "item" that has multiple instances (vanilla bean, vanilla_bean, vanillabean, etc). Either way, you're going to have to put in some heavy lifting initially.

Even so, you could copy the column, tailor it down to all unique values by removing duplicates, and then use a program like Notepad++ or TextPad to copy in them all into a VBA script. It could be done fairly quickly using block mode. Utilizing one of those programs, you could input the proper VBA script before and after each named instance to quickly create several hundred lines of code.

How many unique items do you have? I understand there are about 70,000 lines, but how many unique "items" (not values) within those? The example you gave was Vanilla Bean and Vanilla Ice Cream. How many other types are there?

Example:
Range("A:A").Replace What:="Vanilla_Beans", Replacement:="Vanilla Beans"
Range("A:A").Replace What:="VanillaBeans", Replacement:="Vanilla Beans"

 

[RodneyC]

Duplicates, and the rows they are in, contain data needed for other formulas so they can't be removed. Since this data will be updated weekly, with hundreds of entries being changed/added/deleted each week, it may be easier to make sure the three partners we're working with have checks on their side to make sure entries in column C are consistent.

Having said that, what if we removed the Vanilla Bean vs Vanilla_Bean issue from the equation and simply want to count how many unique entries there are? I've filtered the data on the RevneueALL tab so I am only looking at one value in $B:$B, one value in $H:$H and one value only values >0 in $P:$P. the "Vanilla" issue does not com into play in this scenario and I know what the resulting number should be if we look at RevenueALL$C:$C for unique values. Taking away the "Vanilla" issue, how would I modify this:

=COUNTIFS(RevenueALL!$B:$B,Sheet4!$C12, RevenueALL!$H:$H,$C$2,RevenueALL!$P:$P,">0")

so that it looks here RevenueALL$C:$C for the unique values after calculating everything else and reports that number?

THANK YOU for your continued help.