Formula based on What List Number equals...

[gvillepa]

Hi,

I defined a list (1,2,3,4,5,6,7,8,9,10,11,12,13) which represents Week Number for a sales goal attainment report.

Based on what the user selects, it uses a formula to sum that number of weeks cumulative goal and sums that number of weeks actual sales.

It's a long formula and curious if there were better ways of doing this.

Here's the formula, where cell D10 = the defined list number of 1 thru 13.

You'll notice cells aren't adjacent, but i can make them adjacent if it helps for an easier formula. Thanks!

=IF($D$10=1,I2,IF($D$10=2,(I2+K2),IF($D$10=3,(I2+K2+M2),IF($D$10=4,(I2+K2+M2+O2),IF($D$10=5,(I2+K2+M2+O2+Q2),IF($D$10=6,(I2+K2+M2+O2+Q2+S2),IF($D$10=7,(I2+K2+M2+O2+Q2+S2+U2),IF($D$10=8,(I2+K2+M2+O2+Q2+S2+U2+W2),IF($D$10=9,(I2+K2+M2+O2+Q2+S2+U2+W2+Y2),IF($D$10=10,(I2+K2+M2+O2+Q2+S2+U2+W2+Y2+AA2), IF($D$10=11,(I2+K2+M2+O2+Q2+S2+U2+W2+Y2+AA2+AC2),IF($D$10=12,(I2+K2+M2+O2+Q2+S2+U2+W2+Y2+AA2+AC2+AE2),IF($D$10=13,(I2+K2+M2+O2+Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2))))))))))))))

 

[Marcelo Branco]

Maybe this simpler formula

=SUMPRODUCT(--(MOD(COLUMN(I2:AG2)-COLUMN(I2)+1,2)=1),--(COLUMN(I2:AG2)-COLUMN(I2)+1<=2*D10-1),I2:AG2)

Hope this helps

M.