• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with tracking number of records by date, month, year

milansk

New Member
Hi, I have an excel table where first row is with dates from today's date (11/7/2023) and it continues with tomorrow's date (11/8/2023) and so on almost indefinitely...
Each date has two sub-columns called DM and Operator.
At the end of the table the total records input for DM and Operator in the table are counted with COUNTIF.

In the same spreadsheet I have another table where these records need to be sum up by month (Nov-23, Dec-23, Jan-24 and so on). I need help with this table because I don't know how to make the records to be automatically summed up separately for DM and Operator as I input records every day.

The spreadsheet is attached below. Please let me know if you can help me.
 

Attachments

  • example.xlsx
    37.6 KB · Views: 6
P.S. I would also like to know how can each and every cell be auto-filled with a current date and time as a comment when some data is entered into each cell.
 
Hi, I have an excel table where first row is with dates from today's date (11/7/2023) and it continues with tomorrow's date (11/8/2023) and so on almost indefinitely...
Each date has two sub-columns called DM and Operator.
At the end of the table the total records input for DM and Operator in the table are counted with COUNTIF.

In the same spreadsheet I have another table where these records need to be sum up by month (Nov-23, Dec-23, Jan-24 and so on). I need help with this table because I don't know how to make the records to be automatically summed up separately for DM and Operator as I input records every day.

The spreadsheet is attached below. Please let me know if you can help


We can sum up the records by month for both DM and Operator, you can use a combination of functions like SUMIFS and MONTH. Here's a general guide:

Assuming your date column is in column A, and the corresponding DM and Operator columns are in B and C, respectively:

1. Create a new column for Month and Year: In an empty column, use the formula `=TEXT(A2, "mmm-yy")` and drag it down for all rows. This will give you the month-year format.

2. Sum the records by month for DM: In your new table where you want the monthly sum for DM, use the formula in the first cell (assuming your new month-year column is in column E):

=SUMIFS(B:B, D:D, "DM", A:A, E2)

Drag this formula down for all months.

3. Sum the records by month for Operator: Similarly, in your new table where you want the monthly sum for Operator, use the formula (assuming your new month-year column is in column F):

=SUMIFS(C:C, D:D, "Operator", A:A, F2)

Drag this formula down for all months.

This way, as you input new records each day, the monthly sums for DM and Operator will be updated automatically based on the corresponding month-year in your new tables. Adjust column references based on your actual spreadsheet layout.
 
Back
Top