Counta on dynamic Filter

[hnd12000]

What is the way to have the Filter function (dynamic range on Office 365) to return 0 instead of 1 when the filter finds nothing ?

in whether I put "" in If empty of the FILTER or not, COUNTA still counts 1 :

=FILTER(Database[Number],Database[Submitted Date]>'3PF dashboard'!C1) -> #CALC! which is normal because no items meet the condition but
=COUNTA(FILTER(Database[Number],Database[Submitted Date]>'3PF dashboard'!C1)) -> 1

=FILTER(Database[Number],Database[Submitted Date]>'3PF dashboard'!C1,"") -> "" (empty) but
=COUNTA(FILTER(Database[Number],Database[Submitted Date]>'3PF dashboard'!C1,"") -> 1

As a result I can't differentiate between the genuine cases where the count should be 1 and the one where none are found

 

[MARK858]

Welcome to the forum, can you post a sample of your data using the boards XL2BB addin (the XL2BB icon in the reply window is a link to the download and instructions), making sure that you check the "Show formulas" and click the "Generate output" so we can see the formulas and what the names refer to.

 

[Peter_SSs]

See if this way works for you.

=IFERROR(ROWS(FILTER(Database[Number],Database[Submitted Date]>'3PF dashboard'!C1)),0)

 

[Fluff]

Another way

 =COUNT(FILTER(ROW(Database[Number]),Database[Submitted Date]>'3PF dashboard'!C1))

 

[hnd12000]

=IFERROR(ROWS(FILTER(Database[Number],Database[Submitted Date]>'3PF dashboard'!C1)),0)

Perfect Peter Thank You !

 

[hnd12000]

Another way

 =COUNT(FILTER(ROW(Database[Number]),Database[Submitted Date]>'3PF dashboard'!C1))

Yes Fluff, this works as well !

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

Actually, if your Database heading of 'Number' is a true reflection of what is in that column (that is numbers, not text) then just changing COUNTA to COUNT in your original formula should also work shouldn't it?

=COUNT(FILTER(Database[Number],Database[Submitted Date]>'3PF dashboard'!C1))

 

[hnd12000]

No. Data is text. So only Counta could work.
I just couldn't figure out how to deal with the no filter found and ended up with a super complex multi cell solutions until you guys came up with the ROWS & ROW solution above.
My principle : if you need 2 cells to calculate something, it means that there is 1 to many.