Help with if statement (more than 7)

[Blessedx3]

Awhile back, I came here and found a formula for an IF statement that had
more than 7 IFs. I put it into my spreadsheet and it worked perfectly...well
I saved over it and now it's gone

I hope someone here can get me the correct formula again. Here is my problem:
cell AR3 contains the month (1 for July, 2 for August, etc) and changes each
month. Cells BR4:BR19 contain year-to-date totals for our 16 water systems
from the prior year. The IF statement was something like =IF(AR$3<=1, AT4),
IF(AR$3<=2, AT4+AV4), IF(AR$3<=3, AT4+AV4+AX4), etc...

Obviously, this isn't correct because it doesn't work...LOL! I need column
BR to only add January when AR3=1, add January + February when AR3=2, add
January + February + March when AR3=3, etc.

Clear as mud, right? If anyone can help, I will be so very grateful!!!

THANKS!!!

 

[jcheko]

this is the original formula with the 7 if's......

=IF(A14='Foreman''s DTC(7)'!$D$12,'Foreman''s
DTC(7)'!$E$39,IF(A14='Foreman''s DTC(7)'!$F$12,'Foreman''s
DTC(7)'!$G$39,IF(A14='Foreman''s DTC(7)'!$H$12,'Foreman''s
DTC(7)'!$I$39,IF(A14='Foreman''s DTC(7)'!$J$12,'Foreman''s
DTC(7)'!$K$39,IF(A14='Foreman''s DTC(7)'!$L$12,'Foreman''s
DTC(7)'!$M$39,IF(A14='Foreman''s DTC(7)'!$N$12,'Foreman''s
DTC(7)'!$O$39,IF(A14='Foreman''s DTC(7)'!$P$12,'Foreman''s
DTC(7)'!$Q$39,0)))))))

with the help of somebody in this discussion board....this was the end
result....

=IF(ISNA(MATCH($A10,'DTC(1)'!$12:$12,0)),"",INDEX('DTC(1)'!$37:$37,MATCH($A10,'DTC(1)'!$12:$12,0)))


hope this helps...

 

[Sandy Mann]

Perhaps it was something like:

=IF(AR$3=1,AT4,0)+IF(AR$3=2,AT4+AV4,0)+IF(AR$3=3,AT4+AV4+AX4,0)+...........)

An alternative could be:

=CHOOSE(AR$3,AT4,AT4+AV4,AT4+AV4+AX4,AT4+AV4+AX4+AZ4,AT4+AV4+AX4+AZ4+BB4,
AT4+AV4+AX4+AZ4+BB4+BD4,AT4+AV4+AX4+AZ4+BB4+BD4+BBF4,AT4+AV4+AX4+AZ4+BB4
+BD4+BF4+BH4,AT4+AV4+AX4+AZ4+BB4+BD4+BF4+BH4+BJ4,AT4+AV4+AX4+AZ4+BB4+BD4+
BF4+BH4+BJ4+BL4,AT4+AV4+AX4+AZ4+BB4+BD4+BF4+BH4+BJ4+BL4+BN4,AT4+AV4+AX4+
AZ4+BB4+BD4+BF4+BH4+BJ4+BL4+BN4+BO4)

This chooses from the number in AR3 either:
AT4
AT4+AV4
AT4+AV4+AX4
AT4+AV4+AX4+AZ4
etc

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

Try one of these array formulas** :

If you will *never* insert new columns before the range:

=SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1)),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1)))

If you might insert new columns before the range:

=SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1))-COLUMN(AT4),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Blessedx3]

Sandy,

The first IF statement works perfectly. Thank you!

 

[Blessedx3]

Thanks for your reply.

 

[Blessedx3]

Thanks for your reply.

Try one of these array formulas** :

If you will *never* insert new columns before the range:

=SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1)),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1)))

If you might insert new columns before the range:

=SUM(IF(MOD(COLUMN(AT4:INDEX(AT4:BP4,AR3*2-1))-COLUMN(AT4),2)=0,AT4:INDEX(AT4:BP4,AR3*2-1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks for your reply.