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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Customer​

[/td][td]

Status​

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

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Albert​

[/td][td]

NO​

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

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Albert​

[/td][td]

NO​

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

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Thomas​

[/td][td]

NO​

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

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Albert​

[/td][td]

YES​

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

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Thomas​

[/td][td]

YES​

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

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.