sumproduct formula or replacement

mwvirk

New member
Joined
May 7, 2013
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
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

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 

mwvirk

New member
Joined
May 7, 2013
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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...
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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:

mwvirk

New member
Joined
May 7, 2013
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
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

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,170
Reaction score
12
Points
38
Excel Version(s)
365
Just out of interest, a shorter version of the formula in msg#1 for more recent versions of Excel:
Code:
=SUM((INDEX(I2:FD273,SEQUENCE(272),SEQUENCE(,51,1,3))="")*(INDEX(I2:FD273,SEQUENCE(272),SEQUENCE(,51,2,3))=FX13)*(C2:C273=FZ11))
 
Top