Count If with multiple criteria and unique values

D

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
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.
 
Sort by date Sort by votes

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
This thread has been incredibly helpful, but I have a situation that I don't think has yet been addressed. I need to count unique values for multiple criteria, but the multiple criteria are in the same cell.

For example:

[TABLE="width: 300"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Project[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Project1[/TD]
[TD]John A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Project1[/TD]
[TD]John B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Project1[/TD]
[TD]John C[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Project1[/TD]
[TD]John D[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Project1[/TD]
[TD]John E[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Project2[/TD]
[TD]John A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Project2[/TD]
[TD]John F[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Project2[/TD]
[TD]John G[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Project3[/TD]
[TD]John A[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Project3[/TD]
[TD]John B[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Project3[/TD]
[TD]John F[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Project4[/TD]
[TD]John Z[/TD]
[/TR]
</tbody>[/TABLE]

I'd like to count how many unique individuals worked on both Project1 and Project2. I know how to get the unique contributors to Project1 (5) and unique contributors to Project2 (3) thanks to this thread, but I can't just sum those two, as the total (8) double-counts "John A." The unique contributors to both Project1 and Project2 is only 7.

I'm also hoping the formula used to generate this is scalable - instead of considering only 2 projects, could I consider 4 or more?
 
Upvote 0
ALPPolarbear said:
This thread has been incredibly helpful, but I have a situation that I don't think has yet been addressed. I need to count unique values for multiple criteria, but the multiple criteria are in the same cell.

For example:

[TABLE="width: 300"]
<TBODY>[TR]
[TD="align: center"][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Project
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Project1
[/TD]
[TD]John A
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Project1
[/TD]
[TD]John B
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Project1
[/TD]
[TD]John C
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Project1
[/TD]
[TD]John D
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Project1
[/TD]
[TD]John E
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Project2
[/TD]
[TD]John A
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Project2
[/TD]
[TD]John F
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Project2
[/TD]
[TD]John G
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Project3
[/TD]
[TD]John A
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Project3
[/TD]
[TD]John B
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Project3
[/TD]
[TD]John F
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Project4
[/TD]
[TD]John Z
[/TD]
[/TR]
</TBODY>[/TABLE]

I'd like to count how many unique individuals worked on both Project1 and Project2. I know how to get the unique contributors to Project1 (5) and unique contributors to Project2 (3) thanks to this thread, but I can't just sum those two, as the total (8) double-counts "John A." The unique contributors to both Project1 and Project2 is only 7.

I'm also hoping the formula used to generate this is scalable - instead of considering only 2 projects, could I consider 4 or more?
Click to expand...

The exhibit above shows data in A:B.

[TABLE="width: 66"]
<COLGROUP><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3128" width=88><TBODY>[TR]
[TD="class: xl65, width: 88, bgcolor: transparent"]Project1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Project2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[/TR]
</TBODY>[/TABLE]

D1:D2 houses a set of criterion projects.

D4, control+shift+enter, not just enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($B$2:$B$13<>"",
  IF(ISNUMBER(MATCH($A$2:$A$13,$D$1:$D$2,0)),
  MATCH("~"&$B$2:$B$13,$B$2:$B$13&"",0))),
  ROW($B$2:$B$13)-ROW($B$2)+1),1))
Rich (BB code):
 
Upvote 0
Hi, just joined and posting my first problem.

I need to count how many anatomic targets (coded by numbers in Metastasis columns) different cancer types have.

[TABLE="width: 457"]
<TBODY>[TR]
[TD]Cancer</SPAN>
[/TD]
[TD]Metastasis1</SPAN>
[/TD]
[TD]Metastasis2</SPAN>
[/TD]
[TD]Metastasis3</SPAN>
[/TD]
[TD]Metastasis4</SPAN>
[/TD]
[TD]Metastasis5</SPAN>
[/TD]
[/TR]
[TR]
[TD]Breast</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prostata</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lung</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Liver</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brain</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Breast</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prostata</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lung</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Liver</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brain</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Breast</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prostata</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lung</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Liver</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Brain</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Breast</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

I know that I can do it like this (where I1 tells what anatomic target (from1 to 5) and H1 tells what cancer type is in question):

=SUM(COUNTIFS(B:B,I1,A:A,H1);COUNTIFS(C:C,I1,A:A,H1);COUNTIFS(C:C,I1,A:A,H1)) etc... (cutting formula)

but why I cannot do it like this?

=COUNTIFS(B:F,I1,A:A,H1)

It gives #VALUE! error message. (I hope I remembered english terms right, because I use version of other language)
 
Upvote 0
@penttja

What the first formula does is not clear.

The second formula is illegal, that is, it does not follow the COUNTIFS syntax.
 
Upvote 0
Aladin Akyurek said:
@penttja

1. What the first formula does is not clear.

2. The second formula is illegal, that is, it does not follow the COUNTIFS syntax.
Click to expand...

1.
Sorry, if I'm being unclear. I want to count how many different numbers there are in the columns B to F (Metastasis1, ..., Metastasis5). First I count how many one's (1) there are in the Metastasis1 (column B) column when Cancer is Breast. Then I count (1 AND Breast) from column Metastasis2 (column C) and so on for all 5 columns and then add these together.

2.
Why it is not in syntax? I used it like this: =laske.jos.joukko(B:F;I1;A:A;H1), but replaced laske.jos.joukko = countifs and comma's with semicolon (because I thougth it is like that in english version format). I want to express this: from columns B to F with condition I1 (cell that tells what number we want to count from specified area, eg. 1) AND from column A with condition H1 (eg. Breast). Formula works if I use one column at a time like COUNTIFS(B:B;I1;A:A;H1). So maybe it is not possible to use multiple columns at a time?

I hope this is expressed clearer now.
 
Upvote 0
penttja said:
1.
Sorry, if I'm being unclear. I want to count how many different numbers there are in the columns B to F (Metastasis1, ..., Metastasis5). First I count how many one's (1) there are in the Metastasis1 (column B) column when Cancer is Breast. Then I count (1 AND Breast) from column Metastasis2 (column C) and so on for all 5 columns and then add these together.
Click to expand...

Would you give an example for such a count?

2.
Why it is not in syntax? I used it like this: =laske.jos.joukko(B:F;I1;A:A;H1), but replaced laske.jos.joukko = countifs and comma's with semicolon (because I thougth it is like that in english version format). I want to express this: from columns B to F with condition I1 (cell that tells what number we want to count from specified area, eg. 1) AND from column A with condition H1 (eg. Breast). Formula works if I use one column at a time like COUNTIFS(B:B;I1;A:A;H1). So maybe it is not possible to use multiple columns at a time?

I hope this is expressed clearer now.
Click to expand...

=SUM(IF($A$2:$A$10=H1;IF($B$2:$F$10=I1;1)))

which you must confirm with control+shift+enter, not just enter.
 
Upvote 0
Aladin Akyurek said:
Would you give an example for such a count?


=SUM(IF($A$2:$A$10=H1;IF($B$2:$F$10=I1;1)))

which you must confirm with control+shift+enter, not just enter.
Click to expand...

Thank you very much! Array formula does it much easier than my longer solution. I'm not very familiar with array formula's but maybe I'll figure it out with much appriciated help like yours!

To clear longer solution with COUNTIFS, here is how it goes.

[TABLE="width: 516"]
<TBODY>[TR]
[TD][/TD]
[TD]A</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD]Cancer</SPAN>
[/TD]
[TD]Metastasis1</SPAN>
[/TD]
[TD]Metastasis2</SPAN>
[/TD]
[TD]Metastasis3</SPAN>
[/TD]
[TD]Metastasis4</SPAN>
[/TD]
[TD]Metastasis5</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD]Breast</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD]Prostata</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4</SPAN>
[/TD]
[TD]Lung</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD]Liver</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6</SPAN>
[/TD]
[TD]Brain</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7</SPAN>
[/TD]
[TD]Breast</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>
[/TD]
[TD]Prostata</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9</SPAN>
[/TD]
[TD]Lung</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>
[/TD]
[TD]Liver</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11</SPAN>
[/TD]
[TD]Brain</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12</SPAN>
[/TD]
[TD]Breast</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13</SPAN>
[/TD]
[TD]Prostata</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14</SPAN>
[/TD]
[TD]Lung</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15</SPAN>
[/TD]
[TD]Liver</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]16</SPAN>
[/TD]
[TD]Brain</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>
[/TD]
[TD]Breast</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19</SPAN>
[/TD]
[TD]Cancer</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]4</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[/TR]
[TR]
[TD]20</SPAN>
[/TD]
[TD]Breast</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21</SPAN>
[/TD]
[TD]Prostata</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22</SPAN>
[/TD]
[TD]Lung</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23</SPAN>
[/TD]
[TD]Liver</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24</SPAN>
[/TD]
[TD]Brain</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

=SUM(COUNTIFS(B2:B17;B19;A2:A17;A20);COUNTIFS(C2:C17;B19;A2:A17;A20);COUNTIFS(D2:D17;B19;A2:A17;A20);COUNTIFS(E2:E17;B19;A2:A17;A20);COUNTIFS(F2:F17;B19;A2:A17;A20))

--> result: 2

Above formula counts how many conditions: breast & 1 happens in area defined. But in this formula I have to individually count all the columns that might contain number what I'm counting. In actual data there is 8 columns and your formula does it easier. Thanks!

Do you think it is not possible to do with COUNTIFS easier way that I did? I'm not sure if array formula is the best solution because it might use more resources than possible COUNTIFS solution in case it is a possibility.
 
Upvote 0
penttja said:
Thank you very much! Array formula does it much easier than my longer solution. I'm not very familiar with array formula's but maybe I'll figure it out with much appriciated help like yours!
...
Click to expand...

That's great. I was asking for a count and how that obtains (not for a formula)...
 
Upvote 0
Hi,

I have a similar situation and need your help, please,

1.) In table Y6 to AJ18, the customers are listed in Column A. There are repeat names at times.

2.) Column Z has two locations from where the customer buys.

3) Column AD has the amount.

I need in column AS, the names of the customer as unique entries. That is the name shall not repeat here, unless the same customer has purchased from 2 different locations.
In Column AT I want sum total of the purchases made by them depending upon the unit they purchased from.

Thanks in anticipation.


RGDS,

Rizvi
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
285
RobP
R
R
  • Question Question
Countifs Multiple Criteria
Replies
3
Views
326
Robot1
R
Z
  • Question Question
Vba - create new sheet and copy date into it.
Replies
1
Views
69
zerofeng
Z
D
  • Solved
Formula for column for (sort of) running count
Replies
7
Views
110
Peter_SSs
Peter_SSs
T
  • Solved
Multiple criteria COUNTIFS with distinct values
Replies
4
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,929
Messages
6,175,453
Members
452,643
Latest member
gjcase

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top