• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT result error, arrays have blanks, data in Table

BelindaJoy

BelindaJoy

New Member
I have (attached) a spreadsheet with inventory of parts at 2 shop locations; here called Shop A and Shop B. There is to be a separate inventory count done at each location (columns C and E). The price per part is the same however (column F). We have not priced every item on the inventory list yet, which has left some blanks in column F. I notice that the list I have has blanks (not 0) where we haven't yet counted inventory of a particular item either. Those blanks are being read as "" rather than 0 by the sumproduct formula so I put in logic checks that were supposed to exclude the blanks in either the quantity or price arrays. However when I apply this sum product formula for each shop, I'm not getting the correct answer as checked by helper columns.
The sumproduct for Shop A should come to $399,423.81 but instead is coming to $383,635.59 and I can't figure out why.
=SUMPRODUCT((C5:C1897),--(C5:C1897<>""),(F5:F1897),--(F5:F1897<>""))
I've found that I could solve this by using helper columns instead or by filtering for the "blank" cells and manually clearing those cells, but I'd like to learn what has gone wrong so I can address whatever it is I'm not understanding about this formula and how it works or doesn't work. Is the fact this was given to me formatted as a Table interfering?
 

Attachments

Hi:

Does this do what you want?

=SUMPRODUCT((master[Stock Qty Stony]>0)*(master[Stock Qty Stony]<>""),master[Totals])

Thanks
 
Hi Belinda ,

The formula itself is straightforward :

=SUMPRODUCT(master[Stock Qty Stony]*master[Price EA])

It is the data that is the problem.

1. Select the entire data range B5:F1897

2. Press CTRL H or select Find and Replace.

3. Skip the Find and in the Replace box , enter 0.

4. Click on Replace All.

5. In the Find box , press the Spacebar to enter a single space character.

6. In the Replace box , enter 0.

7. Click on Replace All.

That will do the job.

Narayan
 

Attachments

Thanks, so some of the data includes spaces not just blanks.. that explains the error when I try to just compensate for blanks and that means I do understand the logic behind the formula... thanks.
 
I even found some of the prices at the bottom of the list were being read as text not numbers for some strange reason so I've fixed that too. Once the data has been cleaned of blanks, spaces, "#Values" and text, the formula worked as expected. Thank you again.
 
You must log in or register to reply here.
Back
Top