Search code examples
exceldateformatsumifs

Is there a solution on how to use Excel SUMIFS and TEXT for date?

I have to sum all expenses that match two criteria:

  1. is a specific accounting code ("PK850012")
  2. is a specific month (eg. "August")

Problem is with the 2. criteria. The data comes in a table and the dates are formatted as d/m/yyyy. All expenses that have occured in one month matching the accounting code should be summed.

I tried the following formula that always gives me an error:

=SUMIFS(Table1[Expenses]; Table1[AccCode]; "PK850012"; text(Table1[Date]; "mmmm"); "August")

Seems that TEXT does not work with the data array being tranformed in the formula.

Is there another way to solve this problem? Formatting the Date column to the necessary format is not possible because the data will be replaced frecuently.

Thank you in advance!

Solution

  • Sumproduct is the alternative, try:

    =SUMPRODUCT((MONTH(Table1[Date])=8)*(Table1[AccCode]="PK850012")*(Table1[Expenses]))

    Here is a good tutorial about what's happening behind the scenes

    • remember to mark the answer if it helps.