Sum Product Formula Broke

[SoldierWalb]

I was working on Summing the Qty of all the A orders under the SHP column. This formula was working fine last week and I have been working on some macros in the workbook and noticed that now the formula is no longer adding up the product for me anymore. So in the example below the Count should return a value of 160 for the A and 20 for the B. But instead I am getting a #VALUE! message in both of the boxes.

 

[Fluff]

Check that the numbers in col H are real numbers & not text. Also check you don't have any text in that column

 

[Joe4]

Also check to make sure you do not have any errors in columns C or H.

 

[SoldierWalb]

The information in the cells is pasted into the cells using Paste Options "Values". When I manually move the data to the sheet using Copy and then Paste Values it works correctly and sums the numbers as I wanted it to. But when I run the macro to copy the data to the sheet it just returns the #VALUE. But I'm pretty sure the Macro is using "Paste Values" as the method for putting the data into the sheet.

 

[Fluff]

Do you have any "blank" cells in col H?
Also can you please answer the questions asked.

 

[SoldierWalb]

I do not see any errors in Column C or Column H.
As far as the numbers being real numbers versus they being text. I was not sure exactly how to tell that and thus why I was explaining how the data was getting to the sheet. I just tried to format the cells as numbers and when I do that it makes no difference.

Col H does contain "blank" cells.

But as I mentioned above if I paste the data in the cells manually then the formulas appear to work even with the "Blank" cells in Column H.

 

[SoldierWalb]

I found the error. I was putting a "" in the cells if they did not contain any information. I changed the formula to put a 0 in the blank cells and the formulas started working properly. When I was copying data manually I was only grabbing the cells that contained data and not going down into the blank cells which is why it worked then. I was not copying over any "" cells. Sorry for using your time which this issue.

 

[Fluff]

Glad you sorted it & thanks for the feedback.