R
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
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