Countif formula for criteria where only one value is true for seperate column

edmingtonwoo

New Member
Joined
Jan 23, 2017
Messages
3
Hi all,

Is there any way I can create a Countif formula where I can count a distinct ID once where the criteria would be that the criteria range only has one exact value? For example in this case, I'm trying to create a countif for distinct ID's where the sub_category would be "a" only. Since the rows with ID = 2 or 3 has sub_categories besides "a", the countif should result in '2', whereas ID 1 & 4 satisfies the only "a" criteria.

IDSub_Category
1a
1a
1a
2a
2b
2c
3a
3c
3c
4a
4a
4a

<tbody>
</tbody>

Thanks in advance for the help!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Create a helper column. I used column C and put a as the header.

Assuming Id is in column A and sub cat is column B

Put this in C2 and drag down

=IF($A2=$A1,0,IF(COUNTIFS($A:$A,$A2,$B:$B,C$1)/COUNTIF($A:$A,$A2)=1,1,0))

This formula can be dragged all directions.

Sum the column anywhere except in column C using =SUM(C:C)
 
Upvote 0
Not really sure I follow you, so if I am off the mark, include sample answers in your table please?

Assuming C is correct in his reasoning of what you want, here is another version...
A​
B​
C​
1​
IDSub_Category
2​
1a
3​
3​
1a
4​
1a
5​
2a
1​
6​
2b
1​
7​
2c
1​
8​
3a
1​
9​
3c
2​
10​
3c
11​
4a
3​
12​
4a
13​
4a
C2=IF(OR(A2="",AND(A2=A1,B2=B1)),"",COUNTIFS(A:A,A2,B:B,B2))
copied down
 
Upvote 0
Assuming C is correct in his reasoning


Must be reasoning differently. My formula does this:

IDSub_Categorya
1a1
1a0
1a0
2a0
2b0
2c0
3a0
3c0
3c0
4a1
4a0
4a0

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

The sum of column C is 2.

(Oh, and just a small point: I know It's not obvious from my username and I'm certainly not having a go but thought you might like to know that should have been 'her reasoning') :biggrin:
 
Upvote 0
(Oh, and just a small point: I know It's not obvious from my username and I'm certainly not having a go but thought you might like to know that should have been 'her reasoning') :biggrin:

My apologies, like you, my ID also gives no indication of gender (Im a boy-member though)

And you could very well be spot-on, because I have no real understanding of what the OP wants, just took a guess

(when I read your ID the 1st time, I thought it would be even cuter being "C with no I's" lol)
 
Last edited:
Upvote 0
My real name is Clare spelt without an I. And I wear contact lenses (I'm incredibly shortsighted, -8.5 and -9). Hence, C with no eyes!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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