Sumproduct formula having trouble dealing cell formulas returning blanks

[cwbasset]

I am sure that there is an easy answer for this, but I apparently am not using the correct key search words to find it.

I have a proven conditional sumproduct formula that returns the column value from another spreadsheet: =SUMPRODUCT(('[2021 monthly.xlsx]vlookup'!$C$5:$O$29)*('[2021 monthly.xlsx]vlookup'!$C$3:$O$3=J$4))

However, when I use it on the newer 2022 monthly.xlsx spreadsheet, I receive #VALUE! errors.

I believe the difference must be in the formulas used pulling in data to the range in the original spreadsheets. In the spreadsheet that works, the cell formulas within the sumproduct range use the simple formula of =Jan!$W5.

In the updated spreadsheet, I am using the following formula: =IFERROR(INDEX(Jan!$A$5:$BC$35,MATCH(vlookup!$A5,Jan!$A$5:$A$35,0),MATCH(vlookup!C$1,Jan!$A$4:$BC$4,0)),"")

When I try to evaluate the formula, both ranges used in the formula seem to be fine, it is just at the end when everything comes up with errors. I'm stumped!

Any suggestions would be GREATLY appreciated!

 

[DanteAmor]

),"")

When the formula returns "" empty, then it is no longer considered as a numerical value, so the formula returns error #¡VALUE!

Then you can use the following array formula:

Dante Amor
	K
4	261
Hoja1

Cell Formulas
Range	Formula
K4	K4	=SUM(IF('[2021 monthly.xlsx]vlookup'!$C$3:$O$3=J$4,'[2021 monthly.xlsx]vlookup'!$C$5:$O$29))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cwbasset]

Well, that makes sense. However, I am still receiving the #VALUE! error

 

[DanteAmor]

The formula works with my test data. You could share your test file to review your data.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.