Link to home
Start Free TrialLog in
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
Avatar of Rob Brockett
Rob Brockett
Flag of New Zealand image

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
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
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of agwalsh
agwalsh

ASKER

Thank you to all of the experts here AKA as "the Excel cavalry" :-)
Pleased to help