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.

 

[Aladin Akyurek]

Aladin,
Problem was solved with the following array formula:

{=SUM(IF(FREQUENCY(IF(A2:A23="63X",IF(E2:E23=X21,MATCH(J2:J23,J2:J23,0))),MATCH(J2:J23,J2:J23,0))>0,1))}

Where X21 is where the desired week number is.

Try rather...

=SUM(IF(FREQUENCY(IF($J$2:$J$23<>"",IF($A$2:$A$23="63X",
    IF($E$2:$E$23=X21,MATCH($J$2:$J$23,$J$2:$J$23,0)))),
    ROW($J$2:$J$23)-ROW($J$2)+1),1))

confirmed with control+shift+enter.

 

[markom92]

Try rather...

=SUM(IF(FREQUENCY(IF($J$2:$J$23<>"",IF($A$2:$A$23="63X",
    IF($E$2:$E$23=X21,MATCH($J$2:$J$23,$J$2:$J$23,0)))),
    ROW($J$2:$J$23)-ROW($J$2)+1),1))

confirmed with control+shift+enter.

Works as well!!! Is it a formula that takes less resources from Excel? If so, that'd work perfectly. My worksheet got really slow the moment I used another array formula (MAX/IF), considering my file has 15,000 rows. Still figuring out how to make it more simple in order to lessen the calculation workload on the worksheet.

 

[Aladin Akyurek]

Works as well!!! Is it a formula that takes less resources from Excel? If so, that'd work perfectly.

This avoids in particular counting a blank as a unique item. Regarding performance:

http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html

My worksheet got really slow the moment I used another array formula (MAX/IF), considering my file has 15,000 rows. Still figuring out how to make it more simple in order to lessen the calculation workload on the worksheet.

Is 15,000 chosen as an upperbound for possible growth? What is the exact conditional MAX formula you have?

 

[markom92]

This avoids in particular counting a blank as a unique item. Regarding performance:

http://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html



Is 15,000 chosen as an upperbound for possible growth? What is the exact conditional MAX formula you have?

Affirmative. 15,000 was chosen as an upperbound (didn't consider performance before chosing this). I've reduced it to 7,500 rows as an upperbound (current file has 5,000 rows; information is deleted and new information is introduced each week. So I believe 7,500 could be enough).

Current conditional MAX formula is:

=MAX(IF($Q$2:$Q$7500=Q2,$R$2:$R$7500,))

Consider a set like this:

SKU..........Section
0234ZM....13
0234ZM....23
0234ZM....33

My intention is to know which is the final section of each SKU. That's the input for the unique values formula I asked for earlier.

 

[Aladin Akyurek]

Affirmative. 15,000 was chosen as an upperbound (didn't consider performance before chosing this). I've reduced it to 7,500 rows as an upperbound (current file has 5,000 rows; information is deleted and new information is introduced each week. So I believe 7,500 could be enough).

Current conditional MAX formula is:

=MAX(IF($Q$2:$Q$7500=Q2,$R$2:$R$7500,))

Consider a set like this:

SKU..........Section
0234ZM....13
0234ZM....23
0234ZM....33

My intention is to know which is the final section of each SKU. That's the input for the unique values formula I asked for earlier.

That doesn't look as a big deal. However, if you are willing to run database functions, try DMAX. Also, you might want to work with dynamic named ranges instead of a preset upperbound. Recall also that the formulas with volatile functions like INDIRECT, OFFSET, etc. are costly. Avoid them if possible.

 

[jschvach]

So, I'm trying to count the number of A's in the grade column, but I don't want A's counted twice for the same individual. so I only want to count the A's in reference to a unique ID number.

Column A ................... Column B
ID............................. Grade
370004...................... A
360502...................... A
370005..................... A
370001..................... B
380011..................... D
370016..................... C
380017..................... B
370023..................... C
360502..................... A
370004..................... A

So, This would count 5 A's if I set it up to do a simple count, but I don't want A's counted more than once if it's under the same ID number. So in this example, I'd need to have a formula that would give me a count of 3 A's because those last 2 A's are already accounted for.

 

[Aladin Akyurek]

@jschvach

In F2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$11=$E2,IF(1-($B$2:$B$11=""),
    MATCH($B$2:$B$11$B$2:$B$11,0))),ROW($B$2:$B$11)-ROW($B$2)+1),1))

where E2 houses an ID of interest.

 

[nemosum234]

Hello,

I am trying to generate a segmented repeat customer count from a complete transaction report based on a customer ID and several other criteria.

Column A
Customer Name:
John Doe
Jane Doe
John Doe
Rob Smith
Rob Smith
Jane Doe

Column B
Units Ordered:
1
1
-1
1
1
1

Column C
Date of Order:
12/12/15
12/25/15
12/25/15
11/16/15
05/25/15
12/25/15

Column D
Status:
A
A
A
A
A
A

Column F
Classifier:
Alpha
Alpha
Alpha
Beta
Beta
Alpha

The additional criteria are such that repeat customers are active customers who have placed two or more unique orders on different dates. In the example above, only Rob Smith is counted as a repeat customer, (He placed two orders in Column B, on separate dates Column C, is active Column D, and belongs to the Beta classification Column F).


John Doe is not a repeat customer because his second transaction is a negative value (i.e. a return). Jane Doe is not a repeat customer because her orders were placed on the same date.

I would like to generate a count of unique repeat customers for both the alpha and beta segment based on these criteria. I would also like to flag these accounts for future calculations. Any help with these calculations is appreciated!

Thanks

 

[nemosum234]

bump

 

[tbecker3]

My data is in two columns, A = list of values, B = list of names. How do I count the unique number of values in column A, that match a name in column B?

Thank You! This is a great site.