How to get SumProduct work as I want?

[RTowles]

I feel like I'm making it more complex than it needs to be.

I need a CY report of how many items was sold during each month. I tried using the SUMPRODUCT function, but I keep getting 0, but I need the formula to read the text.

ex.
In Jan - Calculate how many Disposable underpads was sold. and yes, just like in the example there are multiple products with the same name and different product sold numbers.


Please note:
This should be easy and yet I feel that I'm making it much more complicated. Also in my "actual" workbook the data is not in adjacent columns. The product data is in column G and the item number is in Column L.

 

[KenU]

Please see the attached modified file. I added a column for the month sold in order to simplify the formula. It would take a great deal of effort to come up with a good solution without having the month column.

I hope that helps!

Regards,
Ken

 

[RTowles]

That's perfect!

so all I needed to do was add a month column?! Let me work on the formula and hopefully I'll have no more questions!!! Thank you thank you thank you!

 

[RTowles]

if i have blanks in my table will it still calculate.

A1 5
A2 4
A3
A4 6
A5 5

Will it still calculate 20 or only 9?

 

[KenU]

If they are actually blank, then Excel will consider them to have a zero value. However, if they are text (like a space or other non-printing character), that may cause an error.

Ken

 

[RTowles]

i'm getting a #spill error?

 

[pecoflyer]

After making an Excel Table from the data, I created a simple Pivot Table
And, yes, the PT will adapt after adding data. Just click the "refresh" button or add a small refresh macro ( can be found on the Net quite easily)

 

[RTowles]

Hi,

Unfortunately, I cannot use a pivot table or chart. Was looking for a formula to pull the information from one location to another. Thank you for your assistance.

You can close this thread. I'm asking the same question at Mr. Excel and I'm getting a bit more assistance with instructions there. Thank you.

 

[vletm]

RTowles

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.