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)

 

[RockandGrohl]

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

Turns out it was this, as usual you were right on the money.

I spot checked a bunch of cells & used the filter drop downs to check they were numbers. A lot of the blank cells, however, were showing as "TRUE" when I used ISTEXT. I just threw an ISTEXT down across the whole range and found them that way. Now the formula is, as expected, resolving correctly. Thanks again for your help!

 

[RoryA]

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

I think you just happened to have the cell formatted as currency. The result meant that there are 2,150 cells containing something that isn't a number/blank.