Total up values for one month

[Les Stout]

Hello all, i need help desperatly with the following. I have a spread
sheet with values as below in columns A to F and need to total the
values in "F" based on the date in colummn "E". Total for May is 176 and
for April is 99...
I hope that i have explained myself ok, i need to do this with code as
the spread sheet is very long and i have to do from Jan to July...


VPRD 101 8600389020 1 05/05/07 1
VPRD 101 8600388404 1 04/05/07 52
VPRD 101 8600383052 1 04/05/07 4
VPRD 101 8600381932 1 03/05/07 70
VPRD 101 8600378549 1 03/05/07 5
VPRD 101 8600377924 1 02/05/07 44
VPRD 101 8600373000 1 27/04/07 3
VPRD 101 8600372099 1 26/04/07 53
VPRD 101 8600366108 1 26/04/07 4
VPRD 101 8600365754 1 25/04/07 36
VPRD 101 8600360596 1 25/04/07 3

Thank you in advance for any help


Best regards,

Les Stout

 

[Bernd P]

Hello,

=sumproduct(--(year(e2)=year($e$2:$e$999)),--(month(e2)=month($e$2:$e
$999)),$f$2:$f$999)

and copy down.

Regards,
Bernd

 

[Guest]

In G1 enter:
=SUMPRODUCT(--(F$1:F$11),--(MONTH(E$1:E$11)=ROW()))
and copy down

 

[Les Stout]

Thanks Bernd, but this is not what i had in mind...

Best regards,

Les Stout

 

[Les Stout]

Thanks Gary, what i had in mind was some code that loops down the very
long spreadsheet and totals up all May and places the total in a cell,
resets and then totals up for april and so on.

Best regards,

Les Stout

 

[Peter T]

=SUMPRODUCT(Values*(MONTH(Dates)=E15))

Where 'Values' refers to values in your col-F, 'Dates' to dates in your
col-E, in E15 a number representing a month, eg 5 for May

Come back if your really prefer to do with VBA, and why!

Regards,
Peter T

 

[Les Stout]

Hi Peter T, thanks you for your reply... I am not a programmer but
dabble with VBA and have tried without success to do this. The reason is
that i have daily reciepts for parts with an amount and have to Tally up
the months delivery amount and i have to do from Jan 07 to July 07 for
about 1000 different part numbers, so ideally to save time, VBA would be
better.

Best regards,

Les Stout

 

[Peter T]

Hi Les,

Did you try that formula !

A VBA approach, change "E1" to your first date cell, assumes values in the
adjacent col to right

Sub test()
Dim arrTotals(1 To 12, 1 To 2) As Double
Dim arr

With Range("E1")
arr = .Resize(.End(xlDown).Row - .Row + 1, 2)
End With

For i = 1 To UBound(arr)
m = Month(arr(i, 1))
arrTotals(m, 2) = arrTotals(m, 2) + arr(i, 2)
Next

For i = 1 To 12
arrTotals(i, 1) = i
Next

Range("H1:I12").Value = arrTotals

End Sub

This is pretty fast so could be adapted to an array entered UDF, but no
point in preference to the Excel formula.

Regards,
Peter T

 

[Les Stout]

Hi Peter, that works great thank you... Is it possible to put the month
next to the total as well ??

Best regards,

Les Stout

 

[Peter T]

I thought I had done just that, ie months as a numbers next to the totals,
or do you mean spell them out.

Regards,
Peter T

 

[Les Stout]

Sorry to right it out As Jan etc.. Thank you for your help and
patience... It is much appreciated....

Best regards,

Les Stout

 

[Peter T]

That'll cost extra !

In the previous example change -
Dim arrTotals(1 To 12, 1 To 2) As Double
to
Dim arrTotals(1 To 12, 1 To 2) As Variant

(or use two single column arrays, As Double & As String)

and include (instead of the previous loop)
For i = 1 To 12
arrTotals(i, 1) = Format(DateSerial(7, i, 20), "mmm")
Next

You haven't yet said if you actually tried the formula I suggested, and if
so why is the VBA approach is preferred.

Regards,
Peter T

 

[Les Stout]

Hi Peter T, Thanks a million, i did try the formular and was fine
thanks.... The reason i need VBA is that i have to download files from
SAP for various part numbers, with thier goods reciepts for the months
from Jan to July, so to add them up would be easer and quicker... Would
it possible to get this info without even opening the files ?

Best regards,

Les Stout

 

[Peter T]

for the months from Jan to July

Adapt the 12 element array to 6

Would
it possible to get this info without even opening the files ?

Yes but that's a very different subject, start a new thread or search this
ng how to extract data from closed files.

Regards,
Peter T

 

[Peter T]

Would

it possible to get this info without even opening the files ?

Forgot and haven't checked, but pretty sure that formula would work with a
closed file, try it.

Regards,
Peter T