Countif after a date

Megan Chapman

I am trying to count rows from three different sheets if they were entered after 08/1/2021 and summarize them. TY in advance. Inputted dates are an automation and formatted below. Column is named Date Entered.

Kelly Moore

Hey @Megan Chapman

One approach is to use a report if the column names for the date column is the same in all 3 sheets. You can group and summarize the report and use the value in a dashboard.

The 2nd approach is to do this via a formula, you will use 3 Countifs - one for each sheet- and add them together

=COUNTIFS({sheet 1 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)))+COUNTIFS({sheet 2 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)))+COUNTIFS({sheet 3 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)))

Since this is a cross sheet formula, you will need to create the cross sheet reference - you cannot simply copy paste then change the name of the {columns}

cheers

Kelly Moore

Hey @Megan Chapman

One approach is to use a report if the column names for the date column is the same in all 3 sheets. You can group and summarize the report and use the value in a dashboard.

The 2nd approach is to do this via a formula, you will use 3 Countifs - one for each sheet- and add them together

=COUNTIFS({sheet 1 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)))+COUNTIFS({sheet 2 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)))+COUNTIFS({sheet 3 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)))

Since this is a cross sheet formula, you will need to create the cross sheet reference - you cannot simply copy paste then change the name of the {columns}

cheers

Megan Chapman

@Kelly Moore Thank you so much that worked awesome! If I was narrowing down my scope further and wanted to add a HAS(@cell, [Primary Column]@row) to cross check for an additional Condition where would I put that?

Kelly Moore

Hey @Megan Chapman

To clarify, you have a different column in your 3 sheets that contain the same data as the primary column of your main sheet? If yes, the formula would look like this

=COUNTIFS(sheet 1 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)), {sheet1 different column}, HAS(@cell, [Primary Column]@row))+COUNTIFS(sheet 2 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)), {sheet2 different column}, HAS(@cell, [Primary Column]@row))+COUNTIFS(sheet 3 date column}, AND(@cell>DATE(2021,8,1), ISDATE(@cell)), {sheet 3 different column}, HAS(@cell, [Primary Column]@row))

If your primary column isn't named Primary Column, be sure to add the real column name to the formula. And again, you would need to create the new cross sheet reference

Note that the COUNTIFS function has the syntax of (range1, criteria1, range2, criteria2, range3, criteria3...). You can can have one range-criteria pair to infinity - always making sure you add them as a pair to the function as a pair.

cheers,

Kelly

Megan Chapman

Thank you so much!