Share via

How do I calculate the total for the month of January from an array of cell with multiple due dates?

Anonymous
2023-03-22T02:18:16+00:00

I got a list with multiple POs with different due dates and amounts due all the way to December 2023, and I'm trying to compute the total for a specific month let say January. How do I calculate the total for the month of January from an array of cell with multiple due dates?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2023-03-22T08:11:04+00:00

    Column B contains random dates
    E2: =1/1/23
    E3: =EDATE(E2,1)
    drag down

    F2: =SUMIFS($C$2:$C$26,$B$2:$B$26,">="&E2,$B$2:$B$26,"<="&EOMONTH(E2,0))
    drag down

    Sample file:

    https://www.dropbox.com/s/o5f42pk0m0ntp2u/e052c6d7-47ff-4df2-a141-cd7df4a8b706.xlsx?dl=1

    If you want more analyses consider to use a Pivot table, so you can group the dates and use slicers to get the month / quarter / year you want and much more. Give me a sign if you need further help.

    Create a PivotTable to analyze worksheet data - Office Support

    Andreas.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2023-03-22T03:08:29+00:00

    Hi CR16-Galaxy,

    I'm Femi and I'd be happy to help you with your question.

    Firstly, use the MONTH function to identify the cells that contain the due dates for the month of January. This returns the month of a date as a number. For example, the formula =MONTH(B2) returns the month of the date in cell B2.

    Then you can use the SUMIFS function in Excel to sum the corresponding amounts for those dates. =SUMIFS(B1:B10, A1:A10, ">=1/1/2023", A1:A10, "<=1/31/2023") In the formula above, B1:B10 is the range of cells that contains the amounts, and A1:A10 is the range of cells that contains the due dates. The first criterion (">=1/1/2023") specifies that the date must be on or after January 1, 2023, and the second criterion ("<="&1/31/2023) specifies that the date must be on or before January 31, 2023.

    Kindly note that your date format may need to be adjusted in the formula based on your regional settings.

    Then click on enter.

    Best Regards,

    Femi

    Was this answer helpful?

    0 comments