Count If with multiple criteria and unique values

[dixon1983]

Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.

 

[Beafernand]

Here's the actual formula with rows up to 13358. it works great up to 58, but not up to 13358. Thank you for any help you can provide!

=IFERROR(VLOOKUP($B2,$A$1:$O1,6,0),SUM(IF(FREQUENCY(IF($F$2:$F$13385<>"",IF($B$2:$B$13385=$B2,MATCH($F$2:$F$13385,$F$2:$F$13385,0))),ROW($F$2:$F$13385)-ROW($F$2)+1),1)))

 

[Aladin Akyurek]

Here's the actual formula with rows up to 13358. it works great up to 58, but not up to 13358. Thank you for any help you can provide!

=IFERROR(VLOOKUP($B2,$A$1:$O1,6,0),SUM(IF(FREQUENCY(IF($F$2:$F$13385<>"",IF($B$2:$B$13385=$B2,MATCH($F$2:$F$13385,$F$2:$F$13385,0))),ROW($F$2:$F$13385)-ROW($F$2)+1),1)))

What do we have in B and in F? Does the formula start in O2?

 

[Beafernand]

What do we have in B and in F? Does the formula start in O2?

It's the actual formula to my worksheet, but correlates to the table that I had placed as an example earlier:
1) So in my example sheet column A was Customer, where in my real worksheet it's column B as noted in my actual formula.
2) In my example sheet column B for Deployment is really column F in my real worksheet.
3) And O is the column where the formula resides, and where the results get placed...starting in row 2. So O1 is the column header "Unique Deployments", and O2 is the first row with formula and where results are placed.

Formula works beautifully with up to 100 rows or so and where last row in range is 58. The minute I increase the range up to F13385, and copy formula to just 2000 rows, I guess because range is so large upto row 13385, excel is having issues, processing but never ends and I have to keep cancelling excel.

[TABLE="class: cms_table_cms_table_grid, width: 500"]
<tbody>[TR]
[TD]A Customer/B real worksheet
[/TD]
[TD]B Deployment/E real worksheet
[/TD]
[TD]C Contacts[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerA[/TD]
[TD]deploya1[/TD]
[TD]Harry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerA[/TD]
[TD]deploya1[/TD]
[TD]Susie[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CustomerA[/TD]
[TD]deploya2[/TD]
[TD]Harry[/TD]
[/TR]
</tbody>[/TABLE]

 

[Beafernand]

In short, Yes, O2 is where the formula resides and expected results. B is the Customer, and E is the Deployment, both general text data. We are calculating Unique Number of Deployments per Customer.

I am going to try and separate the data sheet from the formula, and place the formula in a different tab/sheet, see if it can process the 13385 rows of data that way. Any other suggestions welcome! I'm thinking there's got to be a way to process this amount of data.
Thank you!

 

[Aladin Akyurek]

In short, Yes, O2 is where the formula resides and expected results. B is the Customer, and E is the Deployment, both general text data. We are calculating Unique Number of Deployments per Customer.

I am going to try and separate the data sheet from the formula, and place the formula in a different tab/sheet, see if it can process the 13385 rows of data that way. Any other suggestions welcome! I'm thinking there's got to be a way to process this amount of data.
Thank you!

O2, control+shift+enter and copy down:

=IF(ISNUMBER(MATCH($B2,$B$1:$B1,0)),VLOOKUP($B2,$B$1:$O1,14,0),
  SUM(IF(FREQUENCY(IF($F$2:$F$13385<>"",IF($B$2:$B$13385=$B2,
  MATCH($F$2:$F$13385,$F$2:$F$13385,0))),
  ROW($F$2:$F$13385)-ROW($F$2)+1),1)))

 

[Beafernand]

Wow! It worked. You are incredible. Thank you. Just one more question, how is that ISNUMBER function solved the processing problem? Just to know for future cases, and to know when to use it versus not. Thank you again! Bea


=IF(ISNUMBER(MATCH($B2,$B$1:$B1,0)),</pre>

 

[Aladin Akyurek]

Wow! It worked. You are incredible. Thank you.

You are welcome.

Just one more question, how is that ISNUMBER function solved the processing problem? Just to know for future cases, and to know when to use it versus not. Thank you again! Bea

=IF(ISNUMBER(MATCH($B2,$B$1:$B1,0)),

If the current B-value is in the above B-range (that is what the above IsNumbe/Match bit verifies), do a VLOOKUP to the already calculated unique count (avoids repeating the heavier count unique calc). Otherwise, effect the required count unique calc.

 

[Beafernand]

Thank you very much!

 

[Aladin Akyurek]

Thank you very much!

You are welcome.

 

[michaelcox2]

Thank you for the help you all posted in this thread, it saved me so much time and frustration!