Overcome Nested IF statements

[Jag Lau]

I have a table as follow on fees and wanted to use the nested IFs to find the accumulated fees depending on which month it is.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Fees[/TD]
[TD]640[/TD]
[TD]500[/TD]
[TD]800[/TD]
[TD]500[/TD]
[TD]650[/TD]
[TD]420[/TD]
[TD]410[/TD]
[TD]550[/TD]
[TD]220[/TD]
[TD]440[/TD]
[TD]160[/TD]
[TD]180[/TD]
[/TR]
</tbody>[/TABLE]

=IF(MONTH(TODAY())=1,B2,IF(MONTH(TODAY())=2,sum(B2:C2),IF(MONTH(TODAY())=3,sum(B2:D2).....

Thats my formula, but because there is a limitation, I cannot test more than 7.

I tried Concatenate formula, but it stopped at the first one.
=CONCATENATE(IF(MONTH(TODAY())=1,B2,""), IF(MONTH(TODAY())=2,sum(B2:C2),""),IF(MONTH(TODAY())=3,sum(B2:D2),"")...

Anyone can help me???

 

[jarjarbingie]

I have a table as follow on fees and wanted to use the nested IFs to find the accumulated fees depending on which month it is.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Fees[/TD]
[TD]640[/TD]
[TD]500[/TD]
[TD]800[/TD]
[TD]500[/TD]
[TD]650[/TD]
[TD]420[/TD]
[TD]410[/TD]
[TD]550[/TD]
[TD]220[/TD]
[TD]440[/TD]
[TD]160[/TD]
[TD]180[/TD]
[/TR]
</tbody>[/TABLE]

=IF(MONTH(TODAY())=1,B2,IF(MONTH(TODAY())=2,sum(B2:C2),IF(MONTH(TODAY())=3,sum(B2:D2).....

Thats my formula, but because there is a limitation, I cannot test more than 7.

I tried Concatenate formula, but it stopped at the first one.
=CONCATENATE(IF(MONTH(TODAY())=1,B2,""), IF(MONTH(TODAY())=2,sum(B2:C2),""),IF(MONTH(TODAY())=3,sum(B2:D2),"")...

Anyone can help me???

=SUM(OFFSET($A$2,0,1,1,MATCH(TEXT(TODAY(),"mmm"),$B$1:$M$1,0)))

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Fees[/TD]
[TD]

640​

[/TD]
[TD]

500​

[/TD]
[TD]

800​

[/TD]
[TD]

500​

[/TD]
[TD]

650​

[/TD]
[TD]

420​

[/TD]
[TD]

410​

[/TD]
[TD]

550​

[/TD]
[TD]

220​

[/TD]
[TD]

440​

[/TD]
[TD]

160​

[/TD]
[TD]

180​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

4470​

[/TD]
[TD][/TD]
[TD]tot fees until august[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


b5=SUMPRODUCT((MONTH(B1:M1&1)<=MONTH(TODAY()))*(B2:M2))

 

[MARZIOTULLIO]

this work for evrey month that you choose for example april

b5=SUMPRODUCT((MONTH(B1:M1&1)<=MONTH(E1&1))*(B2:M2))