YTD No.

[Zubair]

Hi Experts

I want YTD No. in column B tab Sheet 2 from Sheet 3 based on the A1 Sheet 1, please share formula


Sheet 1
A1 = Apr


Sheet 2

Month	YTD No.
Apr	120
Apr	120
Apr	120
May	0

Sheet 3

Jan	31​
Feb	59​
Mar	90​
Apr	120​
May	151​
Jun	181​
Jul	212​
Aug	243​
Sep	273​
Oct	304​
Nov	334​
Dec	365​

 

[jasonb75]

=VLOOKUP(A2,Sheet3!A:B,2,FALSE)

 

[Joe4]

If the months may appear multiple times on Sheet3, and you want to total them up, try this (for the value to return on row 3 of sheet 2):

=SUMIF(Sheet3!A:A,Sheet2!A3,Sheet3!B:B)

 

[Zubair]

Thanks but in May also its giving Apr number, I want Zero if month in column A sheet 2 is > A1 Sheet 1, please see the example

May

0

 

[Joe4]

Thanks but in May also its giving Apr number, I want Zero if month in column A sheet 2 is > A1 Sheet 1, please see the example

Whose formula are you referring to?
According to your example, May should return 151, not 0, as you have an entry on Sheet 3 for May.

If using my formula, just place that formula in cell B3 and copy down, and it will automatically adjust for all other rows.

 

[Zubair]

Hi Joe4 - Yes is showing 151 but I want 0 till A1 sellected May and so on

 

[Joe4]

OK, I see. So try this instead:

=IF(A3=Sheet1!$A$1,SUMIF(Sheet3!A:A,Sheet2!A3,Sheet3!B:B),0)

 

[Zubair]

Extremely sorry for not explaining query completely, yes May is showing Zero but I want Jan to Mar also give 120 with your formula only Apr showing 120.

Means like below

If A1 = Mar
Jan 90
Feb 90
Mar 90
Apr 0
May 0

if A1 = Apr

Jan 120
Feb 120
Mar 120
Apr 120
May 0

if A1 = Jan

Jan 31
Feb 0
Mar 0
Apr 0
May 0

if A1 = Sep

Jan 273
Feb 273
Mar 273
Apr 273
May 273
Jun 273
Jul 273
Aug 273
Sep 273
Oct 0

 

[Joe4]

OK, now I understand the "YTD" nature of this.
The issue is that entries like "Apr", "May", etc are text, and not date, so we really cannot do any math on them "as-is", to say that "Mar" is less than/before "Apr".
So we will have to do some conversions/manipulations on it.

In order to do that, I need to know where in the world you are located, to be sure that the date formats I try to apply will work for your local settings.
In which country are you located?

 

[Zubair]

Pakistan