Issue with COUNTIFS

vipa2000

In row1 of my sheet I have helper text and thus want to exclude from any metrics I generate.

I am currently assembling a sheet with the metrics i want and I have a hit a little snag; my formula is counting the data in row 1. I have:

=COUNTIFS({Surveys and Works - Task ID}, <>"D1", {Surveys and Works - due date}, <=TODAY())

To clarify - I am trying to count all records where due date < todays date, excluding row 1 (I have used the text D1 to exclude)

I then thought if i then add in

=COUNTIFS({Surveys and Works - Task ID}, <>"D1", {Surveys and Works - due date}, <=TODAY(), {Surveys and Works - due date}, <>"") i.e. trying to exclude blanks - no joy

thanks in advance, Paul☺️

vipa2000

Thanks Nic. Worked a treat.

Nic Larsen

Try:

=COUNTIFS({Surveys and Works - Task ID}, <>"D1", {Surveys and Works - due date}, AND(@cell<=TODAY(), NOT(ISBLANK(@cell)

vipa2000

Thanks Nic. Worked a treat.