Is there a formula to list the number of items sold in a month from a long list of data ..

Hi Guys just joined and have a problem.

I have searched the internet for the solution and am getting ready to start head butting the wall. I sure there is a very simple answer to my problem but google just wont give me it!

What I have is two columns, one (Column A) has a list of products in it ie Coats, Shoes, Hat, etc. The other (Column B) just has the Month they were sold in ie, January, well you get the idea.

What I want to do is list how many Coats were sold in each month and so forth for all the products. Is there a formula for this? I'm pretty sure there is but I'm getting nowhere is google or guessing.

Any help would be most appreciated!

Thanks for you help and apologies if its just me being thick.

SUMPRODUCT will do it if you upload a book ill write the formula.

essencally is =sumproduct(((Range1="hats")*(Range2="january")),Range3)

where range 1 is your products rnage 2 is your months and range 3 is your slaes

Use Text function Column-C for getting the Month from columnb. Like =TEXT(A2,"MMMM") and use pivot Table to get the summary of each month's sale.

Try this...

Data in the range A2:B21

Summary area:

D2:Dn = unique items
E1:P1 = month names, Jan, Feb, Mar, etc.

Enter this formula in E2:

=SUMPRODUCT(--($A$2:$A$21=$D2),--($B$2:$B$21=E$1))

Copy across to P2 then down as needed.

Apologies I've not given all the information. SUMPRODUCT does the counting fine individually. I forgot to mention on some days I sell more than one product and I have a thrid column with a number in it. Would I just multiply the SUMPRODUCT by that number? Or can it take that number into account? Sorry again.

@ Sixthsense I don't understand what a pivot table is sorry.

Originally Posted by 3o3

Apologies I've not given all the information. SUMPRODUCT does the counting fine individually. I forgot to mention on some days I sell more than one product and I have a thrid column with a number in it. Would I just multiply the SUMPRODUCT by that number? Or can it take that number into account? Sorry again.

I don't understand your layout.

Can you post a SMALL sample file that shows us the results you expect?

I've neve seen a "dot" used as the sheet delimiter.

You don't need to repeat the sheet name:

=SUMPRODUCT(--(SALES!B5:B1048576="COATS"),--(SALES!A5:A1048576="January"),SALES!C5:C1048576)

If you're referencing that many rows then you're not using Excel 2003 as is displayed in your "user ID" stamp.

If you're uisng Excel 2007 or later then you can use the more efficient SUMIFS function:

=SUMIFS(sales!C:C,sales!B:B,"COATS",sales!A:A,"January")

OK, well, I have no experience with that application so I don't know what would work and what wouldn't work.