COUNTIFS with blank cells

[NJS1982]

Hi, I have a formula which works well to count everything but cells with 'Paid' in them:

=IF(A5="","",COUNTIFS('Payment Tracker'!A:A,A5,'Payment Tracker'!L:L,"<>*Paid*"))

However, I want to also exclude from the count cells which are blank, so I tried this:

=IF(A5="","",COUNTIFS('Payment Tracker'!A:A,A5,'Payment Tracker'!L:L,"<>*Paid*")+COUNTIFS('Payment Tracker'!A:A,A5,'Payment Tracker'!L:L,"<>"))

It does not seem to be excluding the blank cells; does anyone know why this is or how I can fix it?

Thanks!

 

[wideboydixon]

How about this:

=IF(A5="","",COUNTIFS('Payment Tracker'!A:A,A5,'Payment Tracker'!L:L,"<>*Paid*",'Payment Tracker'!L:L,"<>"))

WBD

 

[NJS1982]

Perfect! Thank you

 

[NJS1982]

Actually...one more tweak, if possible...
If column L:L is quite fluid, is there a way to use a MATCH formula to look through the 'Payment Tracker' sheet and find the 'Status' column (typically, column L, but not always I am finding!)?

A bit like this...

=IF(A5="","",COUNTIFS('Payment Tracker'!A:A,A5,MATCH("Status",'Payment Tracker'!$A$1:$EA$1,0),"<>*Paid*",MATCH("Status",'Payment Tracker'!$A$1:$EA$1,0),"<>"))

 

[Aladin Akyurek]

Try...

=IF(A5="","",
    COUNTIFS('Payment Tracker'!A:A,A5,
      INDEX('Payment Tracker'!A:XFD,0,MATCH("status",INDEX('Payment Tracker'!A:XFD,1,0),0)),"<>*Paid*",
      INDEX('Payment Tracker'!A:XFD,0,MATCH("status",INDEX('Payment Tracker'!A:XFD,1,0),0)),"<>"))

 

[NJS1982]

PERFECT! Many thanks.

 

[Aladin Akyurek]

PERFECT! Many thanks.

You are welcome.