|
Formula is to long
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)*($X 5:$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:$G 20=$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 |
Formula is to long
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: 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)*($X 5:$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:$G 20=$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 |
Formula is to long
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" wrote: 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: 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)*($X 5:$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:$G 20=$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 |
Formula is to long
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: 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" wrote: 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: 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)*($X 5:$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:$G 20=$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 |
Formula is to long
This is really frightening
LOL! -- Biff Microsoft Excel MVP "Stefi" wrote in message ... 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: 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)*($X 5:$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:$G 20=$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 |
Formula is to long
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 "RKS" wrote: 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)*($X 5:$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:$G 20=$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 |
Formula is to long
Sorry Biff, I couldn't find out the reason for LOL! Explain it, please!
-- Regards! Stefi €˛T. Valko€¯ ezt Ć*rta: This is really frightening LOL! -- Biff Microsoft Excel MVP "Stefi" wrote in message ... 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: 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)*($X 5:$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:$G 20=$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 . |
Formula is to long
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. -- Biff Microsoft Excel MVP "Stefi" wrote in message ... Sorry Biff, I couldn't find out the reason for LOL! Explain it, please! -- Regards! Stefi "T. Valko" ezt ķrta: This is really frightening LOL! -- Biff Microsoft Excel MVP "Stefi" wrote in message ... 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: 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)*($X 5:$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:$G 20=$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 . |
Formula is to long
Then I'm satisfied with the impact of my word usage.
-- Regards! Stefi €˛T. Valko€¯ ezt Ć*rta: 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. -- Biff Microsoft Excel MVP "Stefi" wrote in message ... Sorry Biff, I couldn't find out the reason for LOL! Explain it, please! -- Regards! Stefi "T. Valko" ezt Ć*rta: This is really frightening LOL! -- Biff Microsoft Excel MVP "Stefi" wrote in message ... 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: 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)*($X 5:$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:$G 20=$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 . . |
| All times are GMT +1. The time now is 03:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com