# How to count all rows on sheet and compare to all rows from yesterday, last week, last month.

Options

Here is what I have for last month:

=COUNTIFS([Request Date]:[Request Date], AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

This seems to work. I am having trouble getting it to look at yesterdays totals and compare to todays total. Also, last weeks total to todays total. And last Months total to todays total.

Any help would greatly be appreciated.

• Employee
Options

Hi Chris,

I'd be happy to help with the formulas related to counting totals for today, yesterday, last week, and last month.

You can simplify the formula you have above by removing the DATE function since the TODAY() function itself is already in a DATE format which the COUNTIFS formula can use.

You can use variations of the following formulas to see if these work for you:

Counting totals for Today:

=COUNTIF([Request Date]:[Request Date], TODAY())

Counting totals for Yesterday:

=COUNTIF([Request Date]:[Request Date], TODAY() - 1)

Counting totals for Last Week:

=COUNTIFS([Request Date]:[Request Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, [Request Date]:[Request Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

Counting totals for Last Month:

=COUNTIFS([Request Date]:[Request Date], IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, [Request Date]:[Request Date], IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

For comparisons, you can subtract one formula from another to get the difference between the two. For example, to compare yesterday's total to today's total you can try the following:

=COUNTIF([Request Date]:[Request Date], TODAY() - 1) - COUNTIF([Request Date]:[Request Date], TODAY())

In case the difference between the totals is a negative number and you want this always to show up as a positive number, you can consider using an absolute value function by placing an ABS function around the entire formula:

=ABS(COUNTIF([Request Date]:[Request Date], TODAY() - 1) - COUNTIF([Request Date]:[Request Date], TODAY()))

For reference, you may visit these links to our Help Center to find more about the above information:

Thanks,

Don

• ✭✭✭✭
Options