Sumproduct resulting in #Value

[GSPRush]

I am trying to summarize two monthly P&Ls (Forecast and Budget) to quarterly P&Ls by using the Sumproduct function. The majority of the line items on these P&Ls are zero balances. I was able to successfully utilize this function to summarize the Plan P&L however the Forecast P&L is resulting in #Value errors. Both the Forecast and Plan P&Ls are similar formats so im not sure what is driving the error. I am using the following formula to source the Plan data (cell L413) =SUMPRODUCT(('Fiscal Plan'!$EP$10:$EP$57=$A413)*('Fiscal Plan'!$ES$7:$FD$7>=data!$H$3)*('Fiscal Plan'!$ES$7:$FD$7<=data!$I$3)*('Fiscal Plan'!$ES$10:$FD$57)). The following formula is being used to source the forecast data (cell B413) =SUMPRODUCT(('Fiscal Forecast'!$FU$10:$FU$57=$A413)*('Fiscal Forecast'!$FX$7:$GI$7>=data!$H$3)*('Fiscal Forecast'!$FX$7:$GI$7<=data!$I$3)*('Fiscal Forecast'!$FX$10:$GI$57)). The "Data" tab reference in the formula sources the dates. I have included screen shots of the all the tabs reference in the formulas above. The forecast formula seems to work if i hard code numbers into the sales line on the forecast sheet. I dont know if zeros are causing the issue but the Plan data seems to work with zeros. Appreciate the help!

Forecast Sheet

Plan Sheet

Data tab

 

[Peter_SSs]

Can't tell much from an image, but the first things that I would check for are, on the Forecast sheet

• Any #VALUE! errors on that sheet in the range $FX$10:$GI$57
• Any text values on that sheet in the range $FX$10:$GI$57 (Sales line?)

 

[GSPRush]

I checked and their are no errors or text formats within the range. Is their a way to send you the file?

 

[Peter_SSs]

Is their a way to send you the file?

You can upload it to somewhere like DropBox, OneDrive, Google Drive etc and provide a publicly shared link here. Make sure any sensitive information is removed or disguised.
.. or you could provide smallish sample section(s) with XL2BB