Create a formula that calculates the sum of a range based on matching to a date

How can I set up a formula that calculates a total from a range based on matching a date from a dropdown. I've been experimenting with Sumifs but can't get it to work.. In the attached file I have entered what I want the answer to be depending on what I choose from the list. Thank you as always.
EE_Cumulative_YTD_Show.xlsx
LVL 1
agwalshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob BrockettCommented:
hi,

This could be accomplished using Offset, which is a volatile function, but my preference is to use Index which isn't volatile.

When your headers are limited to 12 months for the year beginning in January you can use:
=SUM($C5:INDEX($C5:$N5,1,MONTH($Q$3)))

Open in new window


If your headers could extend for more than 12 months, or if the financial year could start in a different month, it may be more robust to match the date in the dropdown against the headers:
=SUM($C5:INDEX($C5:$N5,1,MATCH($Q$3,$C$3:$N$3,0)))

Open in new window


Rob
SteveCommented:
Should work with sumif easily enough.

=SUMIF(DateColumn, Dropdown, ValueColumn)

E.G.
=SUMIF(D1:D5,E1,C1:C5)

Where D1:D5 is the range of dates
E1 is the dropdown
C1:C5 is the range you want to sum (which is aligned with the corresponding dates in D1:D5)

May be awkward with dates as cells can be formatted differently, or date formats can differ. best to set these cells to a specific date format to ensure they match up.
Roy CoxGroup Finance ManagerCommented:
This works for me

=SUMIF($C$3:$N$3,"<=" &DATEVALUE(TEXT(Q3,"mmm-yy")),$C$5:$N$5)
EE_Cumulative_YTD_Show.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor Commented:
Thank you to all of the experts here AKA as "the Excel cavalry" :-)
Roy CoxGroup Finance ManagerCommented:
Pleased to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.