Year to Date calculation formula

[Assadniazi]

Hi

I need help.

In the attached document, I want the cell B5 to show the sum of the months from January until the date (the cell value in B4).


So for example, If in cell B4 I select June, so cell B5 should so sum from January until June (i.e. From B8 until G8).

Please help.

Thanks,
Assad

[63falcondude]

Try this in B5:

=SUM(B8:INDEX(B8:M8,MATCH(B4,B7:M7,0)))

[Assadniazi]

I pasted this in the excel sheet but it is using empty cells in the excel sheet. Can you please provide the formulas for the attached sheet?

I would be very grateful.

[Special-K]

63falcondude's formula works fine for me

in B5
=SUMPRODUCT((TEXT("01/"&B7:M7,"mm")<=TEXT("01/"&B4,"mm"))*(B8:M8))

[63falcondude]

I did...

In your sample from post #1, you have the "select month" in B4, the numbers in B8:M8, and the corresponding month names in B7:M7.

[Zer0Cool]

See attached.

This is much more simple if instead of text for months you use date values and format them to read as month. I did this for your headers in row 7 using the formula (making each header technically current year (2018), months 1-12 and each the first day of the month (1)):

Please Login or Register  to view this content.

and then made B4 a drop down validation list using those headers. I formatted B4 and row 7 using custom format of 'mmmm' to display them as full month name.

Then your formula is simply in B5:

Please Login or Register  to view this content.

Since the start of the range you want the total from is always Jan, this gets the total for everything from the month in B4 (and less than or equal to it) back to January inclusive.