Avatar of agwalsh
agwalsh
 asked on

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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Roy Cox

8/22/2022 - Mon
Rob Brockett

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
Steve

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.
ASKER CERTIFIED SOLUTION
Roy Cox

THIS SOLUTION 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
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
agwalsh

ASKER
Thank you to all of the experts here AKA as "the Excel cavalry" :-)
Your help has saved me hundreds of hours of internet surfing.
fblack61
Roy Cox

Pleased to help