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

[edmingtonwoo]

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.

[TABLE="width: 148"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Sub_Category[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for the help!

 

[C with no eyes]

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)

 

[FDibbins]

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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

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

1​

[/td][td]ID[/td][td]Sub_Category[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]1[/td][td]a[/td][td]

3​

[/td][/tr]

[tr][td]

3​

[/td][td]1[/td][td]a[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]1[/td][td]a[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]2[/td][td]a[/td][td]

1​

[/td][/tr]

[tr][td]

6​

[/td][td]2[/td][td]b[/td][td]

1​

[/td][/tr]

[tr][td]

7​

[/td][td]2[/td][td]c[/td][td]

1​

[/td][/tr]

[tr][td]

8​

[/td][td]3[/td][td]a[/td][td]

1​

[/td][/tr]

[tr][td]

9​

[/td][td]3[/td][td]c[/td][td]

2​

[/td][/tr]

[tr][td]

10​

[/td][td]3[/td][td]c[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]4[/td][td]a[/td][td]

3​

[/td][/tr]

[tr][td]

12​

[/td][td]4[/td][td]a[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]4[/td][td]a[/td][td][/td][/tr]
[/table]
C2=IF(OR(A2="",AND(A2=A1,B2=B1)),"",COUNTIFS(A:A,A2,B:B,B2))
copied down

 

[C with no eyes]

Assuming C is correct in his reasoning

Must be reasoning differently. My formula does this:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: center"]ID[/TD]
[TD="class: xl65, width: 64, align: center"]Sub_Category[/TD]
[TD="width: 64, align: center"]a[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]1[/TD]
[TD="class: xl65, width: 64, align: center"]a[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]1[/TD]
[TD="class: xl65, width: 64, align: center"]a[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]1[/TD]
[TD="class: xl65, width: 64, align: center"]a[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]2[/TD]
[TD="class: xl65, width: 64, align: center"]a[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]2[/TD]
[TD="class: xl65, width: 64, align: center"]b[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]2[/TD]
[TD="class: xl65, width: 64, align: center"]c[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]3[/TD]
[TD="class: xl65, width: 64, align: center"]a[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]3[/TD]
[TD="class: xl65, width: 64, align: center"]c[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]3[/TD]
[TD="class: xl65, width: 64, align: center"]c[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]4[/TD]
[TD="class: xl65, width: 64, align: center"]a[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]4[/TD]
[TD="class: xl65, width: 64, align: center"]a[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: center"]4[/TD]
[TD="class: xl65, width: 64, align: center"]a[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

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')

 

[FDibbins]

(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')

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)

 

[C with no eyes]

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!

 

[FDibbins]

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!

aahh then that makes perfect sense, nice 1