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
EE_Cumulative_YTD_Show.xlsx
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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:
Rob
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)))
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)))
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.
=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.
This works for me
=SUMIF($C$3:$N$3,"<=" &DATEVALUE(TEXT(Q3,"mmm-yy
EE_Cumulative_YTD_Show.xlsx
=SUMIF($C$3:$N$3,"<=" &DATEVALUE(TEXT(Q3,"mmm-yy
EE_Cumulative_YTD_Show.xlsx
Premium Content
You need an Expert Office subscription to comment.Start Free Trial