asked on 3/13/2019

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.

Spreadsheets Microsoft Excel Microsoft Office

Last Comment

Rob Henson

3/15/2019

Ejgil Hedegaard

3/13/2019

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

3/13/2019

ASKER

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

Mark Wood

3/13/2019

ASKER

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

Ejgil Hedegaard

3/13/2019

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

3/13/2019

ASKER

here is a small sample

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

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

3/13/2019

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.

See Pricing Options

Start Free Trial

Norie

3/13/2019

Mark

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

Rob Henson

3/14/2019

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

3/15/2019

ASKER

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?

Just thinking ahead

Ejgil Hedegaard

3/15/2019

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

3/15/2019

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.