Formula is to long

[RKS]

Hi,
I m using one formula in excel 2003. when i entered this formula, "Formula
is to long" message is come. anyone pls short my this formula to otherways.


=IF(AND($A25="",$C25="",$D25<>""),SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21)*($D5:$D20=$D25)*($X5:$X20="O")*($F5:$F20)),
IF(AND($C25="",$D25="",$A25<>""),SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21)*($B$5:$B$20=$A25)*($X5:$X20="O")*($F5:$F20)),
IF(AND($D25="",$A25="",$C25<>""),SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21)*($C$5:$C$20=$C25)*($X5:$X20="O")*($F5:$F20)),
IF(AND($A25<>"",$C25<>"",$D25=""),SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21)*($B$5:$B$20=$A25)*($C$5:$C$20=$C25)*($X5:$X20="O")*($F5:$F20)),
IF(AND($C25<>"",$D25<>"",$A25=""),SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21)*($D5:$D20=$D25)*($C$5:$C$20=$C25)*($X5:$X20="O")*($F5:$F20)),
IF(AND($D25<>"",$A25<>"",$C25=""),SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21)*($D5:$D20=$D25)*($B$5:$B$20=$A25)*($X5:$X20="O")*($F5:$F20)),
IF(AND($A25="",$C25="",$D25=""),SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21)*($X5:$X20="O")*($F5:$F20)),
SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21)*($B$5:$B$20=$A25)*
($C$5:$C$20=$C25)*($D5:$D20=$D25)*($X5:$X20="O")*($F5:$F20)))))))))

RKS

 

[Stefi]

This is really frightening, I'm afraid nobody wants to resolve this formula.
What about to put the task in plain words in order to find a more handy
solution for it?


--
Regards!
Stefi



„RKS†ezt írta:

 

[RKS]

Thanks stefi to respond.

i wnts sum if col f with 3 criteria like a,c & d with all posibilities with
selected period like from date to end date check x col value is "o" . if i
remove col x condition its wrk.
pls help me
rks

 

[Stefi]

In lack of test data not tested, but maybe

=SUMPRODUCT(($G5:$G20>=$D$21)*($G5:$G20<=$H$21),IF(D25<>"",--($D5:$D20=$D25),--($D5:$D20=$D5:$D20)),IF(A25<>"",--($B$5:$B$20=$A25),--($B$5:$B$20=$B$5:$B$20)),IF($C25<>"",--($C$5:$C$20=$C25),--($C$5:$C$20=$C$5:$C$20)),--($X5:$X20="O"),($F5:$F20))

It's an array formula, confirm it with Ctrl+Shift+Enter!

--
Regards!
Stefi



„RKS†ezt írta:

 

[T. Valko]

This is really frightening

LOL!

 

[rzink]

RKS,

WOW. Intense formula! Without disecting your formula, I'd suggest looking
into the "choose" function. With the use of a helper column to determine
which funtion to "choose" from (1-29), this could shorten up your formula.
This also allows the possibility of going beyond the limit of 7 nested IF
statements as well.

rzink

 

[Stefi]

Sorry Biff, I couldn't find out the reason for LOL! Explain it, please!

--
Regards!
Stefi



„T. Valko†ezt írta:

 

[T. Valko]

This is really frightening

Just the aspect that a formula is so long and "ugly" that it's frightening.

That gave me a good LOL.

 

[Stefi]

Then I'm satisfied with the impact of my word usage.
--
Regards!
Stefi



„T. Valko†ezt írta: