# SUMIFS Formula Help In Excel

I have an excel spreadsheet where i need to be able to calculate a total if name =persons name and date is between 2 dates.

I have tried =SUMIFS('2018'!\$C3:\$C2000,"=shell",'2018'!A3:A2000,>=StartDate,'2018'!A3:A2000,<=EndDate,'2018'!D3:D2000)

StartDate and EndDate are named cells on the worksheet.

I have been trying to figure this out but to no avail. I would like to attach a file but unfortunately it has real data in it so that is not possible.

any help would be greatly appreciated.

Last Comment
Rob Henson
Ejgil Hedegaard

The column to sum must be the first argument, guess that is column D
Then try this
=SUMIFS('2018'!D3:D2000,'2018'!\$C3:\$C2000,"=shell",'2018'!A3:A2000,">="&StartDate,'2018'!A3:A2000,"<="&EndDate)
Mark Wood

will this work if the startdate used is from the 2018 sheet and the end date is on the 2019 sheet?
Mark Wood

also when i use =SUMIFS('2018'!D3:D2000,'2018'!\$C3:\$C2000,"=brenda",'2018'!A3:A2000,">="&StartDate,'2018'!A3:A2000,"<="&EndDate) it doesn't work.

all i did was change the name ... any ideas?

other than that it worked great Ejgil
It can be a formatting issue, but without seeing it is difficult to tell.

Can you make a sample file with some data in the used columns.
It does not have to be real values, and a few rows will be enough.
The 2 named ranges must be there.
Mark Wood

here is a small sample

i kinda got stuck figuring this one out because i made a spreadsheet for someone else.
sample.xlsx
Ejgil Hedegaard

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Norie

Mark

You don't seem to have dates for all the rows on the sheets 2018 and 2019.
Rob Henson

You could also use Pivot Table, still need the individual rows populated with dates though.

See attached.

I have added the dates for the 2018 data and have converted the list into a table and deleted the blank rows between the blocks of data.

The total sheet then has a Pivot Table showing the summary for 2018 only.
sample.xlsx
Mark Wood

Sorry for the late reply but that worked perfectly Ejgil. Thanks so much for the help.

On a separate note, would it be possible to have it where you had a StartYear and an EndYear so that it would look at different tabs based on what was selected?

With only one year, indirect formulas could do it, but with 2 or more years as a range, I don't think it is possible.
You could set the date limits for each column, so when dates for 2018 are set, the following years use the same period in that year.
See columns B:C.

In columns G:H are indirect formulas.
The advantage is that it is only one formula in G5, copied to G5:H17.
But indirect formulas are text, and does not update if you insert a column on the data sheets, so generally I would not use that.
Mark-Wood-sample-1.xlsx
Rob Henson

If you just had one data sheet for all data you could use Pivot Table and show as many years as you like as columns.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY