Counting data from previous months?

05/04/21
Answered - Pending Review

I have a table that tracks entries dating back to beginning of time. On a monthly basis, entries are added and removed. I need a simple formula that can give me totals from previous months. All the help I've seen points to a specific date range (count from this date to this date), where I need a total count from the beginning of time all the way to last month.

Answers

  • Jen LangeJen Lange ✭✭✭✭

    Hi @Joe Wootten. Do you want a total of all entries for the previous months by month? Or just a total count of all entries in the sheet at the close of the month?

    Also, can you provide a screenshot of your columns and one sample row of data?

    -Jen

  • Jen LangeJen Lange ✭✭✭✭

    @Joe Wootten , I'm taking a stab without your use case and sheet to guide me. If my assumptions are incorrect, I'll revise my answer to account for any new specifics revealed in your next response.

    To count entries by month, then a supplemental column can be added on your main table to establish this reference for you. I created a column to convert a date into a Month Year reference in the main table. Then in the secondary sheet, report or wherever you're totaling the entries, you can use a COUNTIF formula.

    Entry Month/Year Column:

    =IF(MONTH([Entry Date]@row) = 1, "Jan", IF(MONTH([Entry Date]@row) = 2, "Feb", IF(MONTH([Entry Date]@row) = 3, "Mar", IF(MONTH([Entry Date]@row) = 4, "Apr", IF(MONTH([Entry Date]@row) = 5, "May", IF(MONTH([Entry Date]@row) = 6, "Jun", IF(MONTH([Entry Date]@row) = 7, "Jul", IF(MONTH([Entry Date]@row) = 8, "Aug", IF(MONTH([Entry Date]@row) = 9, "Sep", IF(MONTH([Entry Date]@row) = 10, "Oct", IF(MONTH([Entry Date]@row) = 11, "Nov", IF(MONTH([Entry Date]@row) = 12, "Dec")))))))))))) + " " + YEAR([Entry Date]@row)


    Total Count Column:

    =COUNTIF([Entry Month/Year]:[Entry Month/Year], [Column5]@row)


    Let me know if this is on the right track to meet your objective.

    -Jen

Sign In or Register to comment.