alpeshjain
New Member
- Joined
- Oct 5, 2017
- Messages
- 7
Hi,
I have an excel file like shown below-
Here cell Number B8 and C8 are variable, so when I change them, the some from range B2:M5 should be calculated and showed in cell D8.
I was almost able to do what I am trying to achieve, but the problem is the month names are in string and I need some YTD, so if I select "2016", "June", I need some of 2016 Jan to June.
This will work without issues if the months are in number like 1,2...12. and the formula for the same is-
=SUM(IF($A$2:$A$5=$B$8,IF($B$1:$M$1<=C8),$B$2:$M$5))).
I also have the formula to convert String month to number like - MONTH($C$8&"-1"+0) and can change above to-
=SUM(IF($A$2:$A$5=$B$8,IF($B$1:$M$1<=MONTH($C$8&"-1"+0),$B$2:$M$5)))
But I don't know how can I apply this formula on range "B1:M1".
Any direction or alternate approach is welcome.
Thanks inadvance
Alpesh
I have an excel file like shown below-
![](/board/proxy.php?image=http%3A%2F%2FC%3A%5CUsers%5CAlpesh%5CDesktop%5Ccapture.jpeg&hash=98ded2ca1652087f9086076103e252f3)
Here cell Number B8 and C8 are variable, so when I change them, the some from range B2:M5 should be calculated and showed in cell D8.
I was almost able to do what I am trying to achieve, but the problem is the month names are in string and I need some YTD, so if I select "2016", "June", I need some of 2016 Jan to June.
This will work without issues if the months are in number like 1,2...12. and the formula for the same is-
=SUM(IF($A$2:$A$5=$B$8,IF($B$1:$M$1<=C8),$B$2:$M$5))).
I also have the formula to convert String month to number like - MONTH($C$8&"-1"+0) and can change above to-
=SUM(IF($A$2:$A$5=$B$8,IF($B$1:$M$1<=MONTH($C$8&"-1"+0),$B$2:$M$5)))
But I don't know how can I apply this formula on range "B1:M1".
Any direction or alternate approach is welcome.
Thanks inadvance
Alpesh