Countif, Sumproduct and WeekNum

[theproblem]

Hey there

This is my first post so forgive me if I break too many forum rules. I tried searching everywhere for a solution but to no avail. Perhaps you can help?

I have a table that has 3 columns:
Product name (one of 15 products I have)
Purchase Date (converted to excel datevalue).
Week number (a number between 1 and 52)

I'm trying to figure out how to sum the products purchased in a given week.

Help anyone?

[Paul]

Do you mean you're trying to count the number of products purchased in a given week? I don't see any columns that would have data you could sum.

If so, assuming "Product Name" is column A, "Purchase Date" is column B and "Week Number" is column C, try:

=COUNTIF(C:C,45)

This will count all of the entries in column C that have a value of 45. If you're trying to sum another column, let's say column D has number purchased on that date, you could use SUMIF like so:

=SUMIF(C:C,45,D:D)

[theproblem]

I think I may have failed to explain myself properly.
I've attached a file that shows the data.

In the table above I've written how I'd like to view the data.
In the table below is the data as I have it.

Basically the report has "weeks" for columns and "products" for rows.
In each row it would say how many times the each product mentioned in a given week.

Hope this helps you helps me.

Thank you!

[Paul]

In your example spreadsheet, cell B3, enter the following formula:

=SUMPRODUCT(--($B$10:$B$18=$A3),--($C$10:$C$18=B$2))

You can then copy that down and across to fill your values.

[theproblem]

Well, it works in the test worksheet, but doesn't work in my original...
I'm getting a VALUE error for some hellish reason...


Weird.

Thanks anyway.

[Paul]

Try providing an example worksheet with your REAL layout. You can use fake data, but the formulas depend on the layout, of course.

[theproblem]

Thanks, now I have a whole different problem - bloody week 53 ruins everything. :-)