easy Sumproduct giving value error

[RockandGrohl]

Hello friends

I need to do some kind of sumproduct. The 'OPE' sheet that has the values has the project list in column A, and it has the GL codes across row 1 (starting from C1), with the values for each code & project as a matrix. The sum range is C2:AG863.

On the sheet which will have the formula, the project name is in cell C1, and the GL codes are found in A10:A31

I'm running with

=SUMPRODUCT((OPE!$A$2:$A$863=$C$1)*(OPE!$C$1:$AG$1=$A10)*OPE!$C$2:$AG$863)

which should be everything I need, according to this:

Sum matching columns and rows

To sum values in matching columns and rows, you can use the SUMPRODUCT function. In the example shown, the formula in J6 is: =SUMPRODUCT((codes=J4)*(days=J5)*data) where data (C5:G14), days (B5:B14), and codes (C4:G4) are named ranges. Note: In the latest version of Excel you can also use the...

exceljet.net

But it's giving a #value error.

Similiarly, a multi-criteria XLookup also fails with a value error.

=XLOOKUP(1,(OPE!A2:A863=C1)*(OPE!C1:AG1=A10),OPE!C2:AG863,"",0)

Can anyone help on this? The GL Code I'm looking up is a number (and I've verified both are numbers and not text)

 

[RoryA]

Most likely you have something in C2:AG863 that isn't a number or empty cell.

 

[RockandGrohl]

Most likely you have something in C2:AG863 that isn't a number or empty cell.

Just checked each column and every single value is either a number or empty cell.

 

[RoryA]

Unless you have error values in OPE!A2:A863, OPE!C1:AG1,C1 or A10, I suspect that at least one of the cells you think is empty or a number is actually not what you think. A formula that returns "" for example, would cause a #VALUE! error.

 

[pjmorris]

don't all arrays in the formula need to be the same size?

 

[RockandGrohl]

Unless you have error values in OPE!A2:A863, OPE!C1:AG1,C1 or A10, I suspect that at least one of the cells you think is empty or a number is actually not what you think. A formula that returns "" for example, would cause a #VALUE! error.

Hi Rory,

There's no errors anywhere on the sheet. Column A are strings. Row 1 from C:AG are numbers (and some are text, like some GL codes are numbers and some are words)

Could it be because on the formula sheet the GL codes are in A10:A31, so 21 items, but C:AG is 31 items? Shouldn't be the case because the formula is evaluating each GL code in isolation.

 

[RoryA]

Your formula is fine (I tested it).
Enter:
=COUNTA(OPE!C2:AG863)-COUNT(OPE!C2:AG863)
in a cell and see if it returns 0 or not.

 

[pjmorris]

for what its worth, and to test my previous statement (which was wrong!). I entered:

=SUMPRODUCT((OPE!$A$2:$A$863=$C$1)*(OPE!$C$1:$AG$1=$A10)*OPE!$C$2:$AG$863)

In a new workbook in Cell A1 of Sheet1 and renamed another sheet OPE, no data anywhere. It returned 1.

 

[RockandGrohl]

Your formula is fine (I tested it).
Enter:
=COUNTA(OPE!C2:AG863)-COUNT(OPE!C2:AG863)
in a cell and see if it returns 0 or not.

This returns £2150, can't see why it would return that as the array value is something like 46.5m across all cells.

 

[RockandGrohl]

I used to use a Vlookup and manually entered the cells, but this isn't a good solution as there are more codes being added in every year which misaligns all the cells.