Countif but filter the number of customers...

[albertc30]

Hi all.

I am trying to filter the number of say invoices pending and the formula bellow does this.

=COUNTIF(E:E,"NO")

However, the goal here is to show only the number of customers with outstanding unpaid invoices.

These would be marked on say collumn E as NO.

So, say column D has customers and collumn E has YES or NO.

As an example,

D --------------- E

Albert ------- NO
Albert ------- NO
Thomas ----- NO
Albert ------- YES
Thomas ----- YES

The above would return 2 for the amount of customers with pending invoices.

I have tried the bellow but I'm at a loss here.

{=SUM(--(FREQUENCY(IF(criteria,MATCH(values,values,0)),ROW(values)-ROW(valuesfirstcell)+1)>0))}
Again and as always, many thanks for all of your time.

Cheers.

 

[Marcelo Branco]

Maybe something like this

	D​	E​	F​	G​
1​	Customer​	Status​		Result​
2​	Albert​	NO​		2​
3​	Albert​	NO​
4​	Thomas​	NO​
5​	Albert​	YES​
6​	Thomas​	YES​

Array formula in G2
=SUM(IF(FREQUENCY(IF(TRIM(E$2:E$6)="NO",MATCH(D$2:D$6,D$2:D$6,0)),ROW(D$2:D$6)-ROW(D$2)+1),1))
Ctrl+Shift+Enter

M.

 

[albertc30]

https://www.dropbox.com/s/46wuy8oxohgjr40/Screenshot_6.png?dl=0

I'm having the following error on excell 2019...

How can I ran out of resources with 16GB of ram?

lol...

I'm my own enemy ...

I changed your formula with arrays.

Changed it back, it's now working fine.

Much appreciated for your time and help.

Cheers.