sumproduct formula or replacement

[mwvirk]

need help in this. i am getting 0 result in cell FY13 in my attached file

=SUMPRODUCT(($C$2:$C$273=$FZ$11)*(($I$2:$I$273="")*($J$2:$J$273=$fx$13)+($L$2:$L$273="")*($M$2:$M$273=$fx$13)+($O$2:$O$273="")*($P$2:$P$273=$fx$13)+($R$2:$R$273="")*($S$2:$S$273=$fx$13)+($U$2:$U$273="")*($V$2:$V$273=$fx$13)+($X$2:$X$273="")*($Y$2:$Y$273=$fx$13)+($AA$2:$AA$273="")*($AB$2:$AB$273=$fx$13)+($AD$2:$AD$273="")*($AE$2:$AE$273=$fx$13)+($AG$2:$AG$273="")*($AH$2:$AH$273=$fx$13)+($AJ$2:$AJ$273="")*($AK$2:$AK$273=$fx$13)+($AM$2:$AM$273="")*($AN$2:$AN$273=$fx$13)+($AP$2:$AP$273="")*($AQ$2:$AQ$273=$fx$13)+($AS$2:$AS$273="")*($AT$2:$AT$273=$fx$13)+($AV$2:$AV$273="")*($AW$2:$AW$273=$fx$13)+($AY$2:$AY$273="")*($AZ$2:$AZ$273=$fx$13)+($BB$2:$BB$273="")*($BC$2:$BC$273=$fx$13)+($BE$2:$BE$273="")*($BF$2:$BF$273=$fx$13)+($BH$2:$BH$273="")*($BI$2:$BI$273=$fx$13)+($BK$2:$BK$273="")*($BL$2:$BL$273=$fx$13)+($BN$2:$BN$273="")*($BO$2:$BO$273=$fx$13)+($BQ$2:$BQ$273="")*($BR$2:$BR$273=$fx$13)+($BT$2:$BT$273="")*($BU$2:$BU$273=$fx$13)+($BW$2:$BW$273="")*($BX$2:$BX$273=$fx$13)+($BZ$2:$BZ$273="")*($CA$2:$CA$273=$fx$13)+($CC$2:$CC$273="")*($CD$2:$CD$273=$fx$13)+($CF$2:$CF$273="")*($CG$2:$CG$273=$fx$13)+($CI$2:$CI$273="")*($CJ$2:$CJ$273=$fx$13)+($CL$2:$CL$273="")*($CM$2:$CM$273=$fx$13)+($CO$2:$CO$273="")*($CP$2:$CP$273=$fx$13)+($CR$2:$CR$273="")*($CS$2:$CS$273=$fx$13)+($CU$2:$CU$273="")*($CV$2:$CV$273=$fx$13)+($CX$2:$CX$273="")*($CY$2:$CY$273=$fx$13)+($DA$2:$DA$273="")*($DB$2:$DB$273=$fx$13)+($DD$2:$DD$273="")*($DE$2:$DE$273=$fx$13)+($DG$2:$DG$273="")*($DH$2:$DH$273=$fx$13)+($DJ$2:$DJ$273="")*($DK$2:$DK$273=$fx$13)+($DM$2:$DM$273="")*($DN$2:$DN$273=$fx$13)+($DP$2:$DP$273="")*($DQ$2:$DQ$273=$fx$13)+($DS$2:$DS$273="")*($DT$2:$DT$273=$fx$13)+($DV$2:$DV$273="")*($DW$2:$DW$273=$fx$13)+($DY$2:$DY$273="")*($DZ$2:$DZ$273=$fx$13)+($EB$2:$EB$273="")*($EC$2:$EC$273=$fx$13)+($EE$2:$EE$273="")*($EF$2:$EF$273=$fx$13)+($EH$2:$EH$273="")*($EI$2:$EI$273=$fx$13)+($EK$2:$EK$273="")*($EL$2:$EL$273=$fx$13)+($EN$2:$EN$273="")*($EO$2:$EO$273=$fx$13)+($EQ$2:$EQ$273="")*($ER$2:$ER$273=$fx$13)+($ET$2:$ET$273="")*($EU$2:$EU$273=$fx$13)+($EW$2:$EW$273="")*($EX$2:$EX$273=$fx$13)+($EZ$2:$EZ$273="")*($FA$2:$FA$273=$fx$13)+($FC$2:$FC$273="")*($FD$2:$FD$273=$fx$13)))

[Hercules1946]

Hi
Im not sure if anyone is going to be able to answer this without seeing your spreadsheet. Each (or each pair) of the bracketed expressions will return TRUE (1) or FALSE (0) and I trust you realise that because your multiplying them all, every expression must be TRUE or the final value returned will be a zero. The most likely thing is you have space characters in one or more cells.
If the function is available on your version of Excel, you could try the Evaluate Formula option and look for a FALSE for one of the expressions. Failing that:

(1) Split the ranges into 2 groups (a) = "" and (b) = $fx$13.
(2) For a) Set a COUNTIF for each column and row (cells <> ""). This should pinpoint any such cells by intersection
(3) For b) You can probably check these manually.
(2) split the expressions into individual cells and see which give a FALSE.

HTH

[NBVC]

Crossposted:

http://www.thecodecage.com/forumz/mi...post1055007207

see here for explanation: http://www.excelguru.ca/content.php?184

[mwvirk]

although it's a cross post but only up to a certain extend. i am asking to replace the 'sumproduct' since it's making my sytax too long. other forum which is highlighted by NBVC above, is related to similar formula but getting good support there as well as good response here also. if you believe that it's crosspost, please close this thread here. thank you.

[NBVC]

As mentioned at the Code Cage....

You don't need to close it in either forum, but we need to know that it was posted elsewhere so that people who are giving up free time to help don't go crazy helping you, when you might have received a viable solution already in another forum...

[Hercules1946]

Your narrative in post #1 describes an error in the formula, but there's no mention of your wanting to replace it!
+ No attachment.

[mwvirk]

kindly have a look in the subject.
anyways, thank you for your support. i will go with same formula and i will ask the same previous forum to help. if i need any other help, of course i will post here agin. making sure that it's not cross posted.
thanks again.

[Hercules1946]

Hello
I was making the point that in your own interest in seeking a solution to your problem, you need to explain (in the narrative) the problem itself and your expectations rather than relying on one word in the subject. I thought that the primary objective was to fix the error, leading me in the wrong direction. I would also advise you to post an example spreadsheet as having to construct data tends to put people off.
I hope that you find the solution your looking for.

Hope thats helpful

Hercules

[p45cal]

Just out of interest, a shorter version of the formula in msg#1 for more recent versions of Excel:

=SUM((INDEX(I2:FD273,SEQUENCE(272),SEQUENCE(,51,1,3))="")*(INDEX(I2:FD273,SEQUENCE(272),SEQUENCE(,51,2,3))=FX13)*(C2:C273=FZ11))