Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
In G1 enter:
=SUMPRODUCT(--(F$1:F$11),--(MONTH(E$1:E$11)=ROW())) and copy down -- Gary''s Student - gsnu200735 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
Thanks Bernd, but this is not what i had in mind...
Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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 *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
=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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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 *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
Hi Peter, that works great thank you... Is it possible to put the month
next to the total as well ?? Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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" wrote in message ... Hi Peter, that works great thank you... Is it possible to put the month next to the total as well ?? Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
Sorry to right it out As Jan etc.. Thank you for your help and
patience... It is much appreciated.... Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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" wrote in message ... Sorry to right it out As Jan etc.. Thank you for your help and patience... It is much appreciated.... Best regards, Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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 *** Sent via Developersdex http://www.developersdex.com *** |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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 "Les Stout" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total up values for one month
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 "Les Stout" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help calculated total for each month | Excel Worksheet Functions | |||
Tying names to different values month to month. | Excel Worksheet Functions | |||
Need look up of month to get total for the month | Excel Programming | |||
averaging a total out over a month | Excel Discussion (Misc queries) | |||
12 month Rolling Total | Excel Worksheet Functions |