Count formula with filters

[Ebraham]

Hi,

i have the below data where i want to filter on case column where it is A,B and C and count "Non Client" and NonClient" in Account column...... and than devide it with the total count of A,B and C.

Account	Case
add	A
NonClient	B
125	Peding
NonClient	B
Non Client	C
NonClient	Peding
12426	B

 

[Eric W]

Maybe:

Book1
	A	B	C	D
1	Account	Case
2	add	A		0.6
3	Non Client	B
4	125	Peding
5	NonClient	B
6	Non Client	C
7	NonClient	Peding
8	12426	B
9
Sheet19

Cell Formulas
Range	Formula
D2	D2	=SUM(COUNTIFS(A:A,"Non*Client",B:B,{"A","B","C"}))/SUM(COUNTIF(B:B,{"A","B","C"}))

 

[Ebraham]

thanks it worked as required !!!

Just for understanding * works as optional for space?

 

[Eric W]

Yes, in the IFS functions, a * is a wildcard character, and matches anything. So here it matches either a space or nothing. It will match anything starting with Non and ending with Client, even NonspaceshipClient, but that's unlikely! It's also not case-sensitive. There are ways to check for exactly NonClient or "Non Client" if necessary, this was just quicker.

Glad to help!

 

[Ebraham]

Thanks