Maximum IF statements issue

[ali32j]

Hi All

I am having an issue with maximum number of IF statements which i can use, i have the below formula which effectively checks if a date enterred by user in cell C9 falls in a month (the month is active and changes dependent on a year enterred), i am using two cells to identify the month, so for example

IF(AND(C9>=P7,C9<=Q7),SUM(P9:BK9),

checks to see if C9 falls between dates enterred in P7 and Q7, if it does it then sums the row from this point onwards

Can anyone recommend a better way round this as i have 24months for it check...

CODE:

IF(AND(C9>=P7,C9<=Q7),SUM(P9:BK9), IF(AND(C9>=R7,C9<=S7),SUM(R9:BK9), IF(AND(C9>=T7,C9<=U7),SUM(T9:BK9), IF(AND(C9>=V7,C9<=W7),SUM(V9:BK9), IF(AND(C9>=X7,C9<=Y7),SUM(X9:BK9), IF(AND(C9>=Z7,C9<=AA7),SUM(Z9:BK9), IF(AND(C9>=AB7,C9<=AC7),SUM(AB9:BK9), IF(AND(C9>=AD7,C9<=AE7),SUM(AD9:BK9), IF(AND(C9>=AF7,C9<=AG7),SUM(AF9:BK9), IF(AND(C9>=AH7,C9<=AI7),SUM(AH9:BK9), IF(AND(C9>=AJ7,C9<=AK7),SUM(AJ9:BK9), IF(AND(C9>=AL7,C9<=AM7),SUM(AL9:BK9), IF(AND(C9>=AN7,C9<=AO7),SUM(AN9:BK9), IF(AND(C9>=AP7,C9<=AQ7),SUM(AP9:BK9), IF(AND(C9>=AR7,C9<=AS7),SUM(AR9:BK9), IF(AND(C9>=AT7,C9<=AU7),SUM(AT9:BK9), IF(AND(C9>=AV7,C9<=AW7),SUM(AV9:BK9), IF(AND(C9>=AX7,C9<=AY7),SUM(AX9:BK9), IF(AND(C9>=AZ7,C9<=BA7),SUM(AZ9:BK9), IF(AND(C9>=BB7,C9<=BC7),SUM(BB9:BK9), IF(AND(C9>=BD7,C9<=BE7),SUM(BD9:BK9), IF(AND(C9>=BF7,C9<=BG7),SUM(BF9:BK9),"ERROR")))

Any help would be appreciated

Ali

 

[SkipVought]

Hi,

Is this 24 month period a contiguous 2 year period? ie is it necessary to check each of the 24 month rather than the start and end of the total period?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ali32j]

Not sure what you re asking, basically it checks the start date of a contract to find which month the contract starts, then sums the number of sales achieved since that start date year to date, which will be every month to the right in the table

The date ranges in cells for the sales month changes dependent on the year stated

Is this what you mean?

Ali

 

[xlbo]

what does the formula

=match(C9,P7:BG7,1)

give you?

Should be a number that can be related to the column which holds the max date that is less than the 1 you are searching for (the next date along will be bigger than the date in C9)

You could then use a combination of vlookup and INDIRECT to do all this in 1 go e.g.

if you have a table on a seperate sheet which holds values 16 to 100 in col A and the start column of the range to sum in col B

e.g. A2 = 16, B2 = P

A3 = 17, B3 = R

etc etc

then

=SUM(INDIRECT(vlookup(match(C9,P7:BG7,1),lkuptbl,2,false) & row() & ":BK" & row())

may do what you need - probably need the lookup table configuring to your spec but hopefully you see what I'm getting at...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

[tt]
=SUMPRODUCT
(
(DateRange>=ContractStartDate)*
(DateRange<Date(Year(ContractStartDate),MONTH(ContractStartDate)+25,1)))*
(SumRange)
)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

=SUMIF($P$7:$BK$7,">=" & HLOOKUP($C$9,$P$7:$BK$7,1,TRUE),$P$9:$BK$9)

 

[ali32j]

Hi All

Managed to get it working using xlbo method thanks all for all your help

Ali