formula for year to date position help!

[vba1234]

Hi All

I have data in excel which I need a formaula which calculates the year to date position when a month number is changed. for example

Month
Jan Feb March April..... Dec
5 4 2 3 4

In a input box in excel cell (E4)
if 1 is entered into the box it would return 4 (jan)
if 2 is entered into the box it would return 9 (jan + feb)
...
if 4 is entered into the box it woulld return 14 (jan + feb+mar+april)

Thanks

Hope you can help

[tuph]

Please attach a sample of your workbook so we can use your own data to find possible solutions. Remember to remove or replace and sensitive data before uploading.

[AmirSoft]

Need a sample file

[FDibbins]

Assuming your data looks something like this...

	A	B	C	D	E
1	Jan	Feb	Mar	Apr	May
2	5	4	2	3	4
3
4					4
5					14

E5=SUM(OFFSET($A$2,0,0,1,$E$4))

[vba1234]

Hi

Thank you all for your respond, I have attached a spreadsheet please can you let me know if this is possible.

I just want the 'Call" to be summed up when a month is inputed into the yellow box in the spreadsheet.

Thanks

[vba1234]

Hi

Can anyone help

[samba_ravi]

Your attached file is not clear, give your expected result and some guide lines

[vba1234]

HI nflsales

I have provided more detail in the spreadsheet. Periods are like months, however I just want a formula where if 1 to 13 is inputed it should sum all the values year to date for the 'Call" (row 5) only instead of me doing it on by one evverymonth.

Hope this is much clearer.

Thanks

[samba_ravi]

=SUMPRODUCT(($A$5:$AK$5="Call")*(RIGHT($A$4:$AK$4,LEN($A$4:$AK$4)-7)*1<=$E$1)*($A6:$AK6))

[Henk Stander]

=SUMPRODUCT(($A$5:$AK$5="Call")*(RIGHT($A$4:$AK$4,LEN($A$4:$AK$4)-7)*1<=$E$1)*($A6:$AK6))

@nflsales:
Why does the following formula give incorrect results, i.e. why can't "LEN($A4:$AK$4)-7" not be replaced with "1"?

=SUMPRODUCT(($A$5:$AK$5="Call")*(RIGHT($A$4:$AK$4,1)*1<=$E$1)*($A6:$AK6)),

also what is the purpose of the multiplication by 1?

Great Formula!!

Regards

[samba_ravi]

if time periods are greater then 10 it will give wrong answer ( for 11 it will take 1, for 12, 2 like that)
by using right function it will give text to convert the text into number I multiplied with 1.

[vba1234]

Hi nflsales

I have tired your formula elsewhere and I keep getting value not sure why. I have attached a sample of how the spreadsheet looks and in BD show show the value for 'SFDR" when an number is inputted.

Thanks for your help amazing formula.

Thanks

[Henk Stander]

Hi nflsales

I have tired your formula elsewhere and I keep getting value not sure why. I have attached a sample of how the spreadsheet looks and in BD show show the value for 'SFDR" when an number is inputted.

Thanks for your help amazing formula.

Thanks

@vba1234:
There is nothing wrong with the formula of nflsales, your formula in the attached sample file is supposed to look like:
=SUMPRODUCT(($C$7:$AM$7="SFDR")*(RIGHT($C$6:$AM$6,LEN($C$6:$AM$6)-6)*1<=$E$1)*($C8:$AM8))

Where did you get the "13" from??

Regards,
Henk

[vba1234]

Hi Guys

Sorry for the delayed responds. Happy New Year

Thanks for the help much appreciated.

Thanks :D:D