Selective addition

[buffalo]

Hi,

I have two columns, one with dates and the other with scores.
For each date there are many scores...

Every day I keep adding new scroes for that date. At the end of the day I run a macro --

THis outputs the sum of the scores for today's date on to another sheet.

How would I do that using a worksheet type function?

Thanks,

 

[al_b_cnu]

Hi,

Try the SUMIF function, e.g.

=SUMIF(A1:A18,TODAY(),B1:B18)

HTH

Alan

 

[Richie(UK)]

Hi b,

How about SumIf?

Book2
	A	B	C	D	E
1	Date	Score		Date	Total
2	29-Jan	1		30-Jan	8
3	29-Jan	3
4	30-Jan	5
5	30-Jan	2
6	30-Jan	1
7	31-Jan	2
Sheet1

HTH

 

[buffalo]

Hi works great... actually here is the real problem:
I have TWO columns Date and State, and a third with scores
For each date and state I have many scores.

Now I want to know the sum of scores of every state for today. How would I extend the above?

thanks!

 

[Richie(UK)]

Hi b,

Try SumProduct instead:

Book2
	A	B	C	D	E	F	G
1	Date	State	Score		Date	State	Total
2	29-Jan	A	1		30-Jan	C	6
3	29-Jan	B	3
4	30-Jan	C	5
5	30-Jan	A	2
6	30-Jan	C	1
7	31-Jan	B	2
Sheet1

HTH

 

[buffalo]

Thanks a lot!

 

[buffalo]

Hi I successfulyy used the sumproduct command but I have 10 'states' and
its somehow running very slowly when I do .formula = "=Sumproduct etc.." in VBA

Guess It takes sometime to write down the whole formula in the cell and then calculate it -- its REALLY slow.

ANy suggestions on how I could speed it up in VBA?

 

[PaddyD]

a) define the ranges under consideration
b) post the code you're using
c) explain why you need the vba

 

[buffalo]

Here's the code tothe actual problem

The only problem is its VERY slow...THe code is pretty repetitive. Bogs down everything for some reason.

In the Trade Ticket Data file... I have Date in Col C, Commission in Col N
and Account in Col I.

The names of the account are in Row 2 of the Commissions Sheet. SO for every account we have the Commissions.

I need to use it as a macro. So at the end of the the macro seeks information from the Data sheet and puts the SUM on the Commission sheet, by date and decomposed by Account.
The list grows everytime and only anytime the user executes the macro.


Then instead of Commissions there are other 'scores' and we do the same for thme and this is really slow

(If you would want to look at the actual file -- that'd be great, I could upload it on my web-site)

THanks


Here --> the first column will give today's date..
The next 10 columns will have 10 commission score sums, decomposed by accounts for that day.

Sub Update()

Sheets("Settings").Range("B33").Value = Sheets("Trade Ticket Data").Range("C65536").End(xlUp).Value
--> This stores the date in Settings!B33

'for Commissions
Sheets("Commissions").Range("A65536").End(xlUp).Offset(1, 0).Value = Sheets("Settings").Range("B33").Value

--> THis gives the date in the first non-emty cell of the first column

Sheets("Commissions").Range("A65536").End(xlUp).Offset(0, 1).Formula = "=SUMPRODUCT((('Trade Ticket Data'!C2:C65536)=Settings!B33)*(('Trade Ticket Data'!I2:I65536)=Commissions!B2)*('Trade Ticket Data'!N2:N65536))"
.. gives Commission for Account which is denoted by in Commissions!B2.

and so on.. for 9 other accounts.


End Sub

 

[BuddieB]

you are trying to run the sumproduct formula for nearly every row in the worksheet (65000 times!) try determining the extent fo the range you have actually used on each sheet similar to the method you used to pick the last row of data in column A, use a simiar method and assign that row to a variable, then use that variable in your formula

e.g. "C35:C" & iRowValue

produces the text C35:C25000 if iRowValue = 25000

this will help eliminate unecessary calculation time for XL.