SUMPRODUCT array size not working

[hockey390]

Hello everyone,

I've got a sumproduct formula that is pulling from a large array of data. I paste an updated table of data weekly which sometimes has a varying number of rows. In the past I have had no issue with this scenerio because I can have the forumla range from row 1:10,000 or some row number that I feel will never be reached, but my current formula is not working. I have to manually change the array range (underlined below) to match the actual size of the data array. An example is if I have 1,000 rows, the underlined number would be 1,000. If it only had 999 lines of data next time I update, the size has to be changed to something <=999 or it will error.

=IF($C9="","",IF(ISERROR(SUMPRODUCT((Exhibit!$A$2:$A$1121="BC")*(Exhibit!$H$2:$H$1121=$C9)*(Exhibit!$AA$2:$AA$1121))/(COUNTIFS(Exhibit!$A$2:$A$1121,"BC",Exhibit!$H$2:$H$1121,$C9))),0,SUMPRODUCT((Exhibit!$A$2:$A$1121="BC")*(Exhibit!$H$2:$H$1121=$C9)*(Exhibit!$AA$2:$AA$1121))/(COUNTIFS(Exhibit!$A$2:$A$1121,"BC",Exhibit!$H$2:$H$1121,$C9))))

Here is a clean version of the sumproduct as I have confirmed the countifs is working fine regardless:

=SUMPRODUCT((Exhibit!$A$2:$A$1121="BC")*(Exhibit!$H$2:$H$1121=$C9)*(Exhibit!$AA$2:$AA$1121))

Data in columns is as follows:
Column A - word that is blank if the row has no data and not formula based
Column H - same as column A, but is a number
Column AA - Forumla that returns "" if column A=""

I'm not sure what is wrong, normally I put in a large range with blank data and it is fine. Any help is greatly appreciated.

Thanks,

 

[anglais428]

there is no "=" in the last part of your sumproduct

 

[Jonmo1]

This syntax

SUMPRODUCT((criteria)*(valuerange))

doesn't like formula blanks in the valuerange..

Try this sytax instead, it handles formula blanks as expected

SUMPRODUCT(--(criteria),valuerange)


Hope that helps.

 

[hockey390]

I tried manipulating the data in the range to see if I could pinpoint the issue and it is column AA.

Here is the formula for cell AA1121:

=IF(A1121="","",IF(X1121=0,0,IF(ISERROR(IF(IF(B1121="","",IF(F1121=4,((X1121/Z1121)*VLOOKUP(H1121,'All Items'!$A$2:$AF$500,31,FALSE)),X1121))<1,X1121,IF(B1121="","",IF(F1121=4,((X1121/Z1121)*VLOOKUP(H1121,'All Items'!$A$2:$AF$500,31,FALSE)),X1121)))),"",IF(IF(B1121="","",IF(F1121=4,((X1121/Z1121)*VLOOKUP(H1121,'All Items'!$A$2:$AF$500,31,FALSE)),X1121))<1,X1121,IF(B1121="","",IF(F1121=4,((X1121/Z1121)*VLOOKUP(H1121,'All Items'!$A$2:$AF$500,31,FALSE)),X1121))))))

This is the row after my last row of data. When I put in a 0 (manually override the formula) I get a value for my sumproduct. When I delete everything out of the cell and leave it blank I also get a returned value for my sumproduct. Now I'm even more stumped since cell A1121 is BLANK. I have confirmed this by changing the above formula to return 0 if A1121 is blank, which it does... And then my sumproduct works, but it messes up other formulas because now AA1121 is a value of 0 rather than blank.

 

[hockey390]

This syntax

SUMPRODUCT((criteria)*(valuerange))

doesn't like formula blanks in the valuerange..

Try this sytax instead, it handles formula blanks as expected

SUMPRODUCT(--(criteria),valuerange)


Hope that helps.

Excellent, that fixes it. What does the -- mean or do to the formula? And does the -- have to be before each criteria or just the first if the value range trails a comma?

 

[Jonmo1]

The -- coerces a True/False result into 1/0 (True = 1, False = 0)

You need the -- before each criteria, does not need to be on the sumrange.


The difference is..
SUMPRODUCT((criteria)*(valuerange))
This actually multiplies the criteria with the corresponding value in valuerange.
As an experiment, put a formula in A1 that results in ""
In B1 put = 10*A1
You'll see it fails because of the formula blank.

This syntax
SUMPRODUCT(--(criteria),valuerange)
Keeps the valuerange seperated from the criteria.
It then SUMS each value in valuerange where the criteria was TRUE.
So again, with a formula resulting in Blank in A1
In B1 put =SUM(10,A1)
This works.


Hope that helps.

 

[hockey390]

I appreciate the information, thank you for the help.

 

[barry houdini]

=IF($C9="","",IF(ISERROR(SUMPRODUCT((Exhibit!$A$2:$A$1121="BC")*(Exhibit!$H$2:$H$1121=$C9)*(Exhibit!$AA$2:$AA$1121))/(COUNTIFS(Exhibit!$A$2:$A$1121,"BC",Exhibit!$H$2:$H$1121,$C9))),0,SUMPRODUCT((Exhibit!$A$2:$A$1121="BC")*(Exhibit!$H$2:$H$1121=$C9)*(Exhibit!$AA$2:$AA$1121))/(COUNTIFS(Exhibit!$A$2:$A$1121,"BC",Exhibit!$H$2:$H$1121,$C9))))

As you are using COUNTIFS function you also have access to AVERAGEIFS and IFERROR functions so you could streamline your formula somewhat, try

=IF($C9="","",IFERROR(AVERAGEIFS(Exhibit!$AA$2:$AA$1121,Exhibit!$A$2:$A$1121,"BC",Exhibit!$H$2:$H$1121,$C9),0))