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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
1st post and I'm hoping someone can help me. It's driving me nut that I couldn't really figure this out.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[/TR]
[TR]
[TD]jane[/TD]
[TD]DPPO[/TD]
[/TR]
[TR]
[TD]jane[/TD]
[TD]DPPO[/TD]
[/TR]
[TR]
[TD]kevin[/TD]
[TD]sdi[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD]sdi[/TD]
[/TR]
[TR]
[TD]jimmy[/TD]
[TD]fed[/TD]
[/TR]
[TR]
[TD]jimmy[/TD]
[TD]fed[/TD]
[/TR]
[TR]
[TD]jane[/TD]
[TD]fed[/TD]
[/TR]
</tbody>[/TABLE]

Fixed benefits are: DPPO, SDI, FED

I'm trying to count how many times a particular employee has certain fixed benefits:
The result would look like this:

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[/TR]
[TR]
[TD]jane[/TD]
[TD]DPPO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]jane[/TD]
[TD]DPPO[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]kevin[/TD]
[TD]sdi[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]tom[/TD]
[TD]sdi[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]jimmy[/TD]
[TD]fed[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]jimmy[/TD]
[TD]fed[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]jane[/TD]
[TD]fed[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


any helps are greatly appreciated.

Thanks,
Jimmy
 
Upvote 0
Hello,

Are you simply looking for ... =COUNTIFS(A$1:A$7,A1,$B$1:$B$7,B1)
 
Upvote 0
Hello,

Can someone help me? I am trying to count only the first contact made by a given associate on a given client within a given month. It would look something like the below (with a 1 for Contact being yes, and a 0 no). I have 676 entities, 16 associates, 7 months (so far). I would really want an additional column to the right that just gives me a 1 or 0 if the row contains the first contact (a "unique contact") for that set of criteria (entity, associate, month, contact) so that I can sum them up, attribute them, etc. Can anyone out there assist me with this problem? Thank you!

[TABLE="width: 307"]
<tbody>[TR]
[TD]Entity
[/TD]
[TD]Associate
[/TD]
[TD]Month
[/TD]
[TD]Contact?
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]ABC
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]DEF
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]XYZ
[/TD]
[TD]ABC
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]GHJ
[/TD]
[TD]ABC
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]GHJ
[/TD]
[TD]ABC
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]JKL
[/TD]
[TD]DEF
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]JKL
[/TD]
[TD]DEF
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
@DeltBunyan

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]Entity[/td][td]Associate[/td][td]Month[/td][td]Concat[/td][td]Contact?[/td][/tr]

[tr][td]
2​
[/td][td]XYZ[/td][td]ABC[/td][td]
1​
[/td][td]XYZ|ABC|1[/td][td]
1​
[/td][/tr]

[tr][td]
3​
[/td][td]XYZ[/td][td]DEF[/td][td]
1​
[/td][td]XYZ|DEF|1[/td][td]
1​
[/td][/tr]

[tr][td]
4​
[/td][td]XYZ[/td][td]ABC[/td][td]
1​
[/td][td]XYZ|ABC|1[/td][td]
0​
[/td][/tr]

[tr][td]
5​
[/td][td]GHJ[/td][td]ABC[/td][td]
1​
[/td][td]GHJ|ABC|1[/td][td]
1​
[/td][/tr]

[tr][td]
6​
[/td][td]GHJ[/td][td]ABC[/td][td]
2​
[/td][td]GHJ|ABC|2[/td][td]
1​
[/td][/tr]

[tr][td]
7​
[/td][td]JKL[/td][td]DEF[/td][td]
2​
[/td][td]JKL|DEF|2[/td][td]
1​
[/td][/tr]

[tr][td]
8​
[/td][td]JKL[/td][td]DEF[/td][td]
2​
[/td][td]JKL|DEF|2[/td][td]
0​
[/td][/tr]
[/table]

D2, copied down:

=A2&"|"&B2&"|"&C2

E2, copied down:

=1-ISNUMBER(MATCH($D2,$D$1:D1,0))
 
Upvote 0
Hi Aladin,
Thanks, but this still doesn't solve my problem - I get assignments of a "Unique Contact" all over the place. To be more specific, Entities, Associates, Months, and Contacts can all be out of order, and I have a total of 1708 rows so far... I'm not trying to create a column of "Contact" - rather, I have that column and am trying to create a new column that identifies if that Contact is "unique" - meaning, for example, that the first row I displayed above would not be considered "unique" since it's not even a contact, and the last row I listed above would not be considered "unique" because there already was a contact by Associate DEF with Entity JKL in Month 2. Does that make sense?

Thank you so much for your help!!
 
Upvote 0
DeltBunyan said:
Hi Aladin,
Thanks, but this still doesn't solve my problem - I get assignments of a "Unique Contact" all over the place. To be more specific, Entities, Associates, Months, and Contacts can all be out of order, and I have a total of 1708 rows so far... I'm not trying to create a column of "Contact" - rather, I have that column and am trying to create a new column that identifies if that Contact is "unique" - meaning, for example, that the first row I displayed above would not be considered "unique" since it's not even a contact, and the last row I listed above would not be considered "unique" because there already was a contact by Associate DEF with Entity JKL in Month 2. Does that make sense?

Thank you so much for your help!!
Click to expand...

Please repost the sample along with the results that most obtain.
 
Upvote 0
[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD="align: center"]Entity
[/TD]
[TD]Contact
[/TD]
[TD]Month
[/TD]
[TD]Associate
[/TD]
[TD="align: center"]Unique Contact
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339612939
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]Bar, J
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339831335
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]Bar, J
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339831335
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339831335
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339831335
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339102126
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]Bar, J
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339594067
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339594067
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339591235
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]Bar, J
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339100199
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339279390
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]Bar, J
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339279390
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]Bar, J
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339279390
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]Med, M
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339138746
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]Bar, J
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]Mer, D
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]Mer, D
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]Mer, D
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]Mer, D
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]Zim, C
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]Zim, C
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 76"]
<tbody>[TR="class: grid"]
[TD="align: center"]339459248
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]Amo, M
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
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,933
Messages
6,175,481
Members
452,647
Latest member
MatthewBiersay

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