4

I am at the end of the financial year again and setting up Excel spreadsheets. I am using a sumifs formula that looks at the date and sums one of the columns if the dates are between certain values. ie add up the values in the Bank Expenses column of all those entries dated between between 1st and 30th April.

My problem is that I need to change the formulae individually each year and it takes a long time! I have tried changing one and copying in various forms but they are complex formulae and it does not work correctly - I have been advised on another site that it is a common problem. If I could set them up so that it just used day and month then the problem would be solved but I know that Excel converts the date to a number to calculate.

I cannot see how to do this using the date as a text column. Does anyone have any ideas.

For complete info., the formula I am using is...

=SUMIFS($E$23:$E$1000,$A$23:$A$1000,">=01/04/2017",$A$23:$A$1000,"<=30/4/2017")

Thanks for any help

2 Answers 2

3

You might be willing to try and substitute the criteria in the SUMIF with a CONCAT/CONCATENATE (depending upon Office version), in this way:

CONCAT(">=01/03/";$A$1)

Then, you put the desired year in $A$1 (or any other free cell). Your OP formula would end up being like this:

=SUMIFS($E$23:$E$1000,$A$23:$A$1000,CONCAT(">=01/04/",$A$1),$A$23:$A$1000,CONCAT("<=30/4/",$A$1))

When you need to change year, just enter the new year in $A$1 and it all should be recalculated.

Kind regards.

1
  • Nice solution, Droid. Mar 21, 2018 at 17:12
0

Create two named ranges StartDate and EndDate then refer to them in your formula.

Ideally, I would suggest converting your data into an Excel Table (aka: ListObject) so that you can use structured references in your formulas. They are much easier to read than range addresses. Also, you don't have to edit your ranges as new data is added because the table extends automatically. SUMIFS using table

If you can't convert your data into an Excel Table, you could still use the named ranges in a formula:

=SUMIFS(E23:$E$1000,A23:$A$1000,">="&StartDate,A23:$A$1000,"<="&EndDate)

SUMIFS using ranges

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .