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

Chris Clem

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.

Thank you in advance!

Don C

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:

• https://help.smartsheet.com/function/today
• https://help.smartsheet.com/function/weeknumber
• https://help.smartsheet.com/function/year
• https://help.smartsheet.com/function/abs

 Thanks,

Don

ChelseaH

@Don C , I have a question about your formulas above.

I am working on calculations for 'last week' and am stuck on the year part of the formula. If we are at the very beginning of the year and last week is last year, how would you put that in your formula?

I considered using TODAY()-7 instead of WEEKNUMBER(TODAY()) - 1, then use that for the year portion of the formula, but I'm not convinced it will calculate that correctly either.

Do you have a solution for me?