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.

 

[rjsparks3]

I am new to this website. The content is veryimpressive. Here is my question:
How can Ireturn a result on each row of my spreadsheet that indicates the number ofunique batches that row's fixture category has on that row's day? So forexample, on a row for a transaction that occurred on 2/28/2016 for a*******_faucet: a value of two would be returned because there were two unique*******_faucet batches that occurred on 2/28/2016.

Context:
1. Eachrow represents a transaction.
2. Oneach row there is a column that defines the transaction's batch ID. Eachtransaction falls into a batch (a batch could have one or many transactions init).
3. Oneach row there is a column that defines the transaction's fixture category(*******_faucet, shower, washing machine, etc (basically a list of waterfixtures).
4. Oneach row there is a column that defines the transaction's date.

Here is ascreen shot of the table with some of the above sample data. Also, here is aread only link to that table in my onedrive.

https://onedrive.live.com/redir?res...authkey=!AMr1mWBRZgBw7PE&v=3&ithint=photo,png
https://onedrive.live.com/redir?page=view&resid=5E4E6894DD509EF9!960&authkey=!AGN3qM6dgZrfo-4

Thanks!!!

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]fixture_type[/td][td]

batch_date​

[/td][td]

batch_id​

[/td][td][/td][td]d-count[/td][/tr]


[tr][td]

2​

[/td][td]*******_faucet[/td][td]

3/30/2016​

[/td][td]

2-18273​

[/td][td][/td][td]

1​

[/td][/tr]


[tr][td]

3​

[/td][td]*******_faucet[/td][td]

2/28/2016​

[/td][td]

2-18249​

[/td][td][/td][td]

2​

[/td][/tr]


[tr][td]

4​

[/td][td]*******_faucet[/td][td]

3/31/2016​

[/td][td]

2-18280​

[/td][td][/td][td]

1​

[/td][/tr]


[tr][td]

5​

[/td][td]*******_faucet[/td][td]

2/25/2016​

[/td][td]

2-18235​

[/td][td][/td][td]

1​

[/td][/tr]


[tr][td]

6​

[/td][td]shower[/td][td]

2/27/2016​

[/td][td]

1-05406​

[/td][td][/td][td]

3​

[/td][/tr]


[tr][td]

7​

[/td][td]shower[/td][td]

2/27/2016​

[/td][td]

1-05406​

[/td][td][/td][td]

3​

[/td][/tr]


[tr][td]

8​

[/td][td]shower[/td][td]

2/27/2016​

[/td][td]

1-05406​

[/td][td][/td][td]

3​

[/td][/tr]


[tr][td]

9​

[/td][td]shower[/td][td]

2/27/2016​

[/td][td]

1-05407​

[/td][td][/td][td]

3​

[/td][/tr]


[tr][td]

10​

[/td][td]*******_faucet[/td][td]

3/30/2016​

[/td][td]

2-18273​

[/td][td][/td][td]

1​

[/td][/tr]


[tr][td]

11​

[/td][td]*******_faucet[/td][td]

3/30/2016​

[/td][td]

2-18273​

[/td][td][/td][td]

1​

[/td][/tr]


[tr][td]

12​

[/td][td]shower[/td][td]

2/27/2016​

[/td][td]

1-05409​

[/td][td][/td][td]

3​

[/td][/tr]


[tr][td]

13​

[/td][td]*******_faucet[/td][td]

2/28/2016​

[/td][td]

2-18250​

[/td][td][/td][td]

2​

[/td][/tr]


[tr][td]

14​

[/td][td]shower[/td][td]

2/27/2016​

[/td][td]

1-05409​

[/td][td][/td][td]

3​

[/td][/tr]


[tr][td]

15​

[/td][td]shower[/td][td]

2/27/2016​

[/td][td]

1-05409​

[/td][td][/td][td]

3​

[/td][/tr]


[tr][td]

16​

[/td][td]shower[/td][td]

2/28/2016​

[/td][td]

1-05425​

[/td][td][/td][td]

1​

[/td][/tr]


[tr][td]

17​

[/td][td]shower[/td][td]

2/28/2016​

[/td][td]

1-05425​

[/td][td][/td][td]

1​

[/td][/tr]


[tr][td]

18​

[/td][td]shower[/td][td]

2/28/2016​

[/td][td]

1-05425​

[/td][td][/td][td]

1​

[/td][/tr]


[tr][td]

19​

[/td][td]shower[/td][td]

3/31/2016​

[/td][td]

1-05501​

[/td][td][/td][td]

2​

[/td][/tr]


[tr][td]

20​

[/td][td]shower[/td][td]

3/31/2016​

[/td][td]

1-05502​

[/td][td][/td][td]

2​

[/td][/tr]


[tr][td]

21​

[/td][td]shower[/td][td]

3/31/2016​

[/td][td]

1-05501​

[/td][td][/td][td]

2​

[/td][/tr]
[/table]

You could have posted B:D yourself using Attachments or the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl instead of pointing to a file in the cloud... That said:

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

=SUM(IF(FREQUENCY(IF($B$2:$B$21=$B2,IF($C$2:$C$21=$C2,
    MATCH($D$2:$D$21,$D$2:$D$21,0))),ROW($D$2:$D$21)-ROW($D$2)+1),1))

 

[rjsparks3]

Aladin, that is awesome! Thanks for the formula.

For some reason when I transfer the formula to my larger workbook it is not working. Can you give me a hand? Here is the file. The key columns are in green. My attempted formula column is in yellow:
https://onedrive.live.com/redir?resid=5E4E6894DD509EF9!968&authkey=!AKm2GkCbh6t1fm4&ithint=file,xlsx

Thank you Aladin!

PS: Based on the link you provided to the attachments, it indicated i could not add an attachment (per the moderator). Also, when I tried to download the add-in my OS gave me a warning, so i stopped there. Sorry if this sounds silly - i am brand new to this site. Thank you again for your help!

 

[rjsparks3]

I was improperly pasting the formula down the sheet - my mistake. I needed to use control D (fill, down) to do that, versus copy paste.

Thanks!

 

[Aladin Akyurek]

I was improperly pasting the formula down the sheet - my mistake. I needed to use control D (fill, down) to do that, versus copy paste.

Thanks!

You are welcome.

 

[tbecker3]

Hey Aladin,

I entered a new post on March 15th, but have not received a reply, so I thought I would reply to you through this one as you were so much help previously. Thanks.

Here is my new request.

I need a Rank function which the inclusion of Rank is based on weather another column in the file is populated with an X.
So, Rank column AF, if Column C contains an X.

I hope you can help!

 

[Aladin Akyurek]

Hey Aladin,

I entered a new post on March 15th, but have not received a reply, so I thought I would reply to you through this one as you were so much help previously. Thanks.

Here is my new request.

I need a Rank function which the inclusion of Rank is based on weather another column in the file is populated with an X.
So, Rank column AF, if Column C contains an X.

I hope you can help!

See your original: http://www.mrexcel.com/forum/excel-questions/928100-rank-preceeding-criteria.html#post4487394

 

[dankeykang]

Hey aladin,

Would you be able to help me out with my seemingly simple issue? I posted it below.

http://www.mrexcel.com/forum/excel-questions/815377-arrays-counting-unique-rows-criteria.html

 

[Aladin Akyurek]

Hey aladin,

Would you be able to help me out with my seemingly simple issue? I posted it below.

http://www.mrexcel.com/forum/excel-questions/815377-arrays-counting-unique-rows-criteria.html

See that thread for the formula you need.

 

[jurgen272]

Hi, I've been struggling for a while now with counting the number of unique values based on different criteria.

example:
A B C D E F G
1 X Office Month Office APR MAY
2 1212100 Office1 APR Office1 3 1
3 1212100 Office1 APR Office2 1 1
4 112122 Office1 APR
5 112122 Office1 MAY
6 123456789 Office1 APR
7 123456789 Office2 APR
8 14598763 Office2 MAY

Which formula can I use in the fields F2, F3, G2, G3?
= number of unique references in column A for a certain Office in column B and for a certain Month in column C

(Total table has x-thousands of lines)

Regards,
Jurgen