0

I am using a summary page to show stats from a registration page. I want to show the number of registrations based on their date of registration (which is in column A of the Registrations page. I modified a formula offered here via a different post:

=countA(FILTER('Reg-6-20-2019'!A$2:A,LEFT('Reg-6-20-2019'!A$2:A,2)="01"))

So this should return all of the registrations that were made in "01" because the exported format from the software is in the mm/dd/yyyy format. It does this fine. I add cells to display 02, 03, 04, 05, 06, 07, 08 All of them work great, but when I get to 07 and 08 (future registrations) which should return 0, it returns a 1.

Not a big deal, but not sure I understand why it would do this. All months that actually have registrations are correct.

It does correctly output a 1 for July if I put an July entry into the data sheet, and a 2 if I put in 2 of them, but why am I getting a 1 if there are no such entries matching the criteria?

0

It's because if there are no entries, then FILTER can't fetch data eg. FILTER will output #N/A error and COUNTA doesn't care and counts #N/A as 1. Therefore try:

=COUNTA(IFERROR(FILTER('Reg-6-20-2019'!A$2:A, LEFT('Reg-6-20-2019'!A$2:A, 2)="01")))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.