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

[GodzFire]

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]

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

 

[GodzFire]

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]

How about

Excel 2013 32 bit
	A	B	C
1	A	x	1
2	B		3
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]

A	x			A	1
B				B	3
C				C	2
D	x			D	1
A
B
C	x
D
C
B
A	x

<tbody>
</tbody>

Let A2:B12 house the sample you posted.

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

=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:

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

 

[GodzFire]

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

SHEET1

A	B	C	D-O	P
A	other data	other data	etc	x
B	other data	other data	etc
C	other data	other data	etc
D	other data	other data	etc	x
A	other data	other data	etc
B	other data	other data	etc
C	other data	other data	etc	x
D	other data	other data	etc
C	other data	other data	etc
B	other data	other data	etc
A	other data	other data	etc	x

<tbody>
</tbody>

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]

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

1​

A

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

x

Sheet1

2​

B

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

3​

C

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

4​

D

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

x

5​

A

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

6​

B

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

7​

C

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

x

8​

D

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

9​

C

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

10​

B

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

11​

A

other data

other data

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

etc

x

<tbody>
</tbody>

	A​	B​	C​	D​
1​	A	1​		Sheet2
2​	B	3​
3​	C	2​
4​	D	1​

<tbody>
</tbody>

Sheet2

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

 

[Aladin Akyurek]

@ 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]

Thank you everyone! It worked!