I have two sheets one with date/times, and names in columns that gets populated automatically from a form submission, and a second one that has dates only across the columns with names down the rows. I am building a countif formula that will count all entries on the form submission page that match the name of the person on the date.

I have made it work for another sheet setup where the entries are added manually through the use of a helper datevalue() cell which pulls the datevalue() from the date and time filled cell and then matches it to the datevalue() of the header cell on the second sheet.

Essentially for each date and name on the grid, it counts if that person did an entry on that day.

My other sheet populates automatically and so I cant use the helper column because the import automation overwrites blank cells using standard criteron doesnt seem to work because of the times in the cells. So having a hard time getting the count to work because it doesnt match the top date.

Calculate based on name in Column A and Date in Row 1 Based on date in Column A and Name in Column B on second sheet

  • Welcome! To consider a date regardless of time, you can use the two conditions "Greater than or equal to the specified date and less than the next day". If I understand your screenshots correctly, it will be something like =COUNTIFS(Sheet1!B:B,$A3,Sheet1!A:A,">="&B$2,Sheet1!A:A,"<"&(B$2+1))
    – JohnSUN
    Jul 7 at 18:06
  • That worked perfectly thank you! Jul 7 at 18:20


Your Answer

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

Browse other questions tagged or ask your own question.