SUMPRODUCT error

Question

Hi

I have a SUMPRODUCT formula that works fine, but when I try to extend the range of the formula, it returns a #VALUE! error.

It's a fairly simple formula: basically, depending on whether the user selects Previous, Current or Next year from a dropdown, it returns costs in 3 columns for a given product. It looks like this (this one works):

=SUMPRODUCT((G$10:G$75=RN1550)*IFS(PnL_CustFY1="Prev", QX$10:QZ$75, PnL_CustFY1="Curr", RD$10:RF$75, TRUE, RJ$10:RL$75))

This one fails:

=SUMPRODUCT((G$9:G$75=RN1550)*IFS(PnL_CustFY1="Prev", QX$9:QZ$75, PnL_CustFY1="Curr", RD$9:RF$75, TRUE, RJ$9:RL$75))

Because I'm also using VBA code to insert rows, I want the formula to look at the range from one row higher, so that if a row is inserted in the first row, formulas don't need to be adjusted. I've done similar with other formulas, that work fine, but this one just won't work for me.

Row 9 includes column headers (text only)

Any suggestions appreciated...

Paul

Answer 1

At this stage, my workaround is to give names to the ranges G$10:G$75, QX$10:QZ$75, RD$10:RF$75 & RJ$10:RL$75, and if a row is inserted (via VBA) to the first or last rows, to extend the range of the name to include the new row (also via VBA). This works well, but the original problem remains a mystery to me.

Answer 2

What is the range contained within PnL_CustFY1?

Hi Vijay, it's a single cell where the user selects Prev, Curr, or Next (FY).

To simplify the problem, the formula could look like this:

=SUMPRODUCT((G$9:G$75=RN1550)*(RD$9:RF$75))

or:

=SUMPRODUCT(--(G$9:G$75=RN1550), RD$9:RF$75)

Currently none of these are working for me. I don't understand why I'm getting a #VALUE! error, given that the arrays are the same number of rows, and given that I have comparable formulas working well.

Answer 3

Hi melbournefilm

Greetings! I am Vijay, an Independent Advisor. I am here to work with you on this problem.

What is the range contained within PnL_CustFY1?

Answer 4

Look at the data. The #Value means there is wrong format data in the formula, ie text instead of number etc.

As I posted, the original formula works, and excludes the headers (text). When I include the headers, it fails.

According to Microsoft's online documentation for SUMPRODUCT, "SUMPRODUCT treats array entries that are not numeric as if they were zeros", and I have used SUMPRODUCT elsewhere to include text without any problems.

Answer 5

Look at the data. The #Value means there is wrong format data in the formula, ie text instead of number etc.

https://exceljet.net/excel-formula-errors#value\_error