SUMPRODUCT #VALUE! error

[amyylouise]

I can't figure out why I'm getting this error only on this one row. All my other formulas on the Revenue Summary sheet work fine.

{=SUMPRODUCT((MONTH(InventoryTracking[Date Sold])='Revenue Summary'!M2)*(InventoryTracking[Sale Price (Before Tax)]))} **no problems with this one**

{=SUMPRODUCT((MONTH(InventoryTracking[Date Sold])='Revenue Summary'!M2)*(InventoryTracking[Profit Received (Tax Excluded)]))} **comes up with #VALUE ! error**

Here's the link to my workbook: https://1drv.ms/x/s!AnG6B6etKkxDgr13YBrU3stJGK1ODA

Help please!

TIA!

 

[Jonmo1]

You have formulas in the Tax Excluded column, some of which are returning "".
"" is NOT actually BLANK. It's an empty text string.
Number*Text=#Value!

Your Before Tax column doesn't contain formulas like that.


FYI, these formulas do not need to be entered as an array.

 

[amyylouise]

You have formulas in the Tax Excluded column, some of which are returning "".
"" is NOT actually BLANK. It's an empty text string.
Number*Text=#Value!

Your Before Tax column doesn't contain formulas like that.


FYI, these formulas do not need to be entered as an array.

OMG thank you so much! This has been driving me crazy all morning!

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(--(MONTH(InventoryTracking[Date Sold])='Revenue Summary'!M2),(InventoryTracking[Profit Received (Tax Excluded)]))

confirmed with just Enter

M.

 

[Aladin Akyurek]

B3, copied down:

=SUMPRODUCT(INDEX(InventoryTracking,0,MATCH($A3,InventoryTracking[#Headers],0)),(TEXT(InventoryTracking[Purchase Date],"mmm")=B$1)+0)