Results 1 to 8 of 8

Thread: sumproduct formula or replacement

  1. #1

    sumproduct formula or replacement



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    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

  3. #3
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016


  4. #4
    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.

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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...


  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Your narrative in post #1 describes an error in the formula, but there's no mention of your wanting to replace it!
    + No attachment.
    Last edited by Hercules1946; 2013-05-08 at 03:34 PM. Reason: omission

  7. #7
    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.

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •