Count occurances of something in a list, but also only if another column doesn't have something in it

GodzFire · Apr 30, 2018

Hello, suppose the following: I have a list of various departments. I want to count how many times each department shows up in a list. However, I am also only looking for it to be counted if another separate column doesn't have an 'x' in it.

What formula could I use for that? Example below:

A x
B
C
D x
A
B
C x
D
C
B
A x

A 1
B 3
C 2
D 1

Fluff · Apr 30, 2018

Have a look at countifs
https://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/

GodzFire · Apr 30, 2018

I had seen and looked at that page, however I openly admit I struggle with formulas, which is why I was hoping someone could kindly assist me here.

Fluff · Apr 30, 2018

How about

Excel 2013 32 bit

A

B

C

1

A

x

1

2

B

3

C

2

4

D

x

1

5

A

6

B

7

C

x

8

D

9

C

10

B

11

A

x

Sheet2

Cell Formulas
Range	Formula
C1	=COUNTIFS(A$1:A$11,A1,B$1:B$11,"")

Aladin Akyurek · Apr 30, 2018

[TABLE="class: grid, width: 384"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]x[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Let A2:B12 house the sample you posted.

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

Rich (BB code):

=IFERROR(INDEX($A$2:$A$12,SMALL(IF(FREQUENCY(IF($A$2:$A$12<>"",MATCH($A$2:$A$12,$A$2:$A$12,0)),
    ROW($A$2:$A$12)-ROW(INDEX($A$2:$A$12,1,1))+1),ROW($A$2:$A$12)-ROW(INDEX($A$2:$A$12,1,1))+1),
    ROWS($E$2:E2))),"")

In F2 just enter and copy down:

Rich (BB code):

=COUNTIFS($A$2:$A$12,E2)-COUNTIFS($A$2:$A$12,E2,$B$2:$B$12,"x")

GodzFire · Apr 30, 2018

Sorry, I didn't explain myself accurately enough, let me try again:

SHEET1

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D-O
[/TD]
[TD="align: center"]P
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]other data
[/TD]
[TD]other data[/TD]
[TD]etc
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]other data[/TD]
[TD]other data
[/TD]
[TD]etc
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]other data
[/TD]
[TD]other data[/TD]
[TD]etc
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]other data[/TD]
[TD]other data
[/TD]
[TD]etc
[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]other data
[/TD]
[TD]other data
[/TD]
[TD]etc
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]other data[/TD]
[TD]other data
[/TD]
[TD]etc
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]other data
[/TD]
[TD]other data
[/TD]
[TD]etc[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]other data
[/TD]
[TD]other data
[/TD]
[TD]etc
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]other data
[/TD]
[TD]other data
[/TD]
[TD]etc
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]other data
[/TD]
[TD]other data
[/TD]
[TD]etc
[/TD]
[TD]x
[/TD]
[/TR]
</tbody>[/TABLE]

SHEET2
A 1
B 3
C 2
D 1

Then on another separate spreadsheet tab (ie SHEET 2), it would show the total count not counting any with an X. Does that make sense?

MARZIOTULLIO · Apr 30, 2018

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A

[/TD]
[TD]

B

[/TD]
[TD]

C

[/TD]
[TD]

D

[/TD]
[TD]

E

[/TD]
[TD]

F

[/TD]
[TD]

G

[/TD]
[TD]

H

[/TD]
[TD]

I

[/TD]
[TD]

J

[/TD]
[TD]

K

[/TD]
[TD]

L

[/TD]
[TD]

M

[/TD]
[TD]

N

[/TD]
[TD]

O

[/TD]
[TD]

P

[/TD]
[TD]

Q

[/TD]
[/TR]
[TR]
[TD]

1

[/TD]
[TD]A[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]x[/TD]
[TD]Sheet1[/TD]
[/TR]
[TR]
[TD]

2

[/TD]
[TD]B[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3

[/TD]
[TD]C[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4

[/TD]
[TD]D[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5

[/TD]
[TD]A[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6

[/TD]
[TD]B[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7

[/TD]
[TD]C[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8

[/TD]
[TD]D[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9

[/TD]
[TD]C[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10

[/TD]
[TD]B[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11

[/TD]
[TD]A[/TD]
[TD]other data[/TD]
[TD]other data[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A

[/TD]
[TD]

B

[/TD]
[TD]

C

[/TD]
[TD]

D

[/TD]
[/TR]
[TR]
[TD]

1

[/TD]
[TD]A[/TD]
[TD]

1

[/TD]
[TD][/TD]
[TD]Sheet2[/TD]
[/TR]
[TR]
[TD]

2

[/TD]
[TD]B[/TD]
[TD]

3

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3

[/TD]
[TD]C[/TD]
[TD]

2

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4

[/TD]
[TD]D[/TD]
[TD]

1

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

B1=COUNTIFS(Sheet1!$A$1:$A$11,Sheet2!$A1,Sheet1!$P$1:$P$11,"<>"&Sheet1!$P$1) copy down

Aladin Akyurek · Apr 30, 2018

@ GodzFire

Have you tried the set up I have provided at all?

Here is the workbook which implements that set up: https://www.dropbox.com/s/2zurzo4zv2msa26/GodzFire count occurrences.xlsx?dl=0

GodzFire · May 2, 2018

Thank you everyone! It worked!

Count occurances of something in a list, but also only if another column doesn't have something in it

GodzFire

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

GodzFire

New Member

Fluff

MrExcel MVP, Moderator

Aladin Akyurek

MrExcel MVP

GodzFire

New Member

MARZIOTULLIO

Well-known Member

Aladin Akyurek

MrExcel MVP

GodzFire

New Member

Similar threads

Forum statistics

Share this page

Count occurances of something in a list, but also only if another column doesn't have something in it

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

MrExcel MVP

New Member

Well-known Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock