Excel 97 & VBA - List Count and Sum

[rocknrisk]

Hi all,

Hope you can help, please. I need to COUNT and SUM items for specific dates, i.e:

Col A Col B
No. of Items Date
45 31/02/03
67 31/02/03
1 31/02/03
19 24/12/02
12 24/12/02
2 24/12/02
37 24/12/02
908 15/05/02
145 15/05/02
435 01/01/02

I need this to then (on a new "Summary" sheet) read:

ColA ColB ColC ColD ColE
31/02/03 3 Sales = 113 Items
24/12/02 4 Sales = 34 Items
15/05/02 2 Sales = 1053 Items
01/01/02 1 Sales = 435 Items

The dates have been sorted in desending order.

Please can someone help me write a macro/code to do this as Excel won't let me do this on it's own.

Any help will be greatly appreciated.

Thank you in advance,
Clinton

"The important thing is not to stop questioning." - Albert Einstein

 

[bluedragon2]

For counting the dates:

=COUNT(<Date Range>,<Date Reference>)

To sum the dates sales:

=SUMPRODUCT((<Date Range>=<Date Reference>)*(<SalesNumber Reference>))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Clinton,

What are you doing with 31/02/03 -- 31 days in FEBRUARY???

If those were REAL DATES (you could have entered actual dates and use the Group feature to Group by Year & Month), you could use the Pivot Table Wizard to create one or two pivot tables that give the results you are looking for...

1. without any formulae
2. in less than 5 seconds!



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bluedragon2]

If you were around, I knew a pivot table reference would be forthcoming

I really need to take a look at their functionality.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

Blue - COUNT or COUNTIF

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[rocknrisk]

Thank you BlueDragon,

Please could you help me with this? How do I get the code to know when the Date changes i.e: <Date Reference>, as I have literally 1000s of rows with almost 700 dates?.

Again, thank you.

Clinton

&quot;The important thing is not to stop questioning.&quot; - Albert Einstein

 

[SkipVought]

Clinton,

The SUMPRODUCT formula does BOOLEAN arithmetic. You enter the appropriate ranges and references and it figgers it all out and gets you the SUM by DATE.

BUT you have to reference EACH INDIVIDUAL DATE!

However, if you use the Pivot Table Wizard, IT DOES ALL THAT! No Formulas!




Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rocknrisk]

Wow, thank you all,

SkipVought - Sorry, I'm stressed - was just picking dates to use for the example. Also, I can't use a PivotTable as I need the data exactly as I mentioned before:

ColA ColB ColC ColD ColE
28/02/03 3 Sales = 113 Items
24/12/02 4 Sales = 34 Items
15/05/02 2 Sales = 1053 Items
01/01/02 1 Sales = 435 Items

This will be uploaded to a reporting program.

xlbo - Closer, I'm getting closer... hey, I feel like Meatloaf ;o)

Thanks again guys

&quot;The important thing is not to stop questioning.&quot; - Albert Einstein

 

[SkipVought]

You CAN use a PivotTable and sort in DESCENDING order by Date!

This whole thing can be accomplished in LESS THAN ONE MINUTE! Do you understand?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Skip - chill. There are instances where a pivottable would not be appropriate - like if you wanted to upload the data elsewhere for instance.

How about this:
Select all your dates
follow menupath
Data>Filter>Advanced Filter

Choose &quot;Copy To Another Location&quot;
Click in the copy To box and select the cell you want your list of unique dates to start in and tick &quot;Unique Records Only&quot;

Note: If you want the list of unique dates on a seperate sheet, you will need a bit of code - there's a bug that doesn't allow you to copy to another sheet manually - but you can in VBA

For counts
Against your 1st date enter
=countif(Sheet1!$B$2:$B$1000,$A2)

where your 1st date is in A2 and your &quot;All Dates&quot; list is on sheet1 and goes from B2:B1000

Then copy down

For your SUMS
Against your 1st date enter
=sumif(sheet1!$B$2:$B$1000,$A2,sheet1!$A$2:$A$1000)

same assumptions as above

Then copy down.....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[bluedragon2]

I created a little example sheet that shows how the two formulas work for you (and yes Geoff, countif ).

Although Skip is correct that a pivot table is simple and easy, but if you want to take a look at the sample sheet, let me know.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Geoff,

The PivotTable gives you the Unique Filter equivalent ALONG WITH a COUNT or SUM, Then just &quot;add&quot; a column for the other.

It's still a simpler approch!



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Skip - I do not doubt that BUT if this is data to be uploaded to Access (or similar), a pivottable wouldn't be appropriate as you would have to do some work afterwards to format for upload - as opposed to work beforehand to prepare the data - I guess it'll work out about the same anyways...

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244