Count if and sum if working days and working hours macro

tuytuy

New member
Joined
Apr 5, 2013
Messages
5
Reaction score
0
Points
0
Location
A, A
Hi,
MonthGSM numberExtensionGroup nameSubgroup nameName GSM numberUser ref1User Ref2Call dateCall timeCall durationDestinationZone/Country/OperatorTariffTypeSupplementary servicesIndicative tariffUsage amount
Jan-130470/132056Unfiled - Niet toegekend - Non attribué19-Dec-1210:51:5100:00:4231624298877Vodafone P-BNormalOutgoing roaming callsR00.203
Jan-130470/132056Unfiled - Niet toegekend - Non attribué29-Dec-1213:10:4500:00:4531624298877Vodafone P-BRéduitOutgoing roaming callsR00.2175
Jan-130470/132056Unfiled - Niet toegekend - Non attribué29-Dec-1214:16:3600:16:1531651275403Vodafone P-BRéduitOutgoing roaming callsR04.7122
Jan-130470/132056Unfiled - Niet toegekend - Non attribué29-Dec-1213:27:0700:00:3031651275403Vodafone P-BRéduitOutgoing roaming callsR00.145

i have a table like above,
i need, using a macro, to count and then sum the total amount used (last column of the table) for all non working days and calls outside working hours (7.00 am to 7.00 pm / 0700 to 1900)
using an excel formula i was thinking of using the D formula on the date and then count only the ones that have a value equal to 6 or 7.
But i'm quite new to vba and i have no idea how to right a vb code to solve this.
 
Tuytuy: This formula should do it:
=SUMPRODUCT(Usage_Amount*(WEEKDAY(Call_Date,2)<6)*((HOUR(Call_Time+Call_Duration)<7)+(HOUR(Call_Time)>19)))
...where Usage_Amount, Call_Date, and Call_Duration are named ranges pointing to those columns.

Or if you turn your data into an excel table (by pushing CTRL+T) then it would be this:
=SUMPRODUCT(Table1[Usage amount]*(WEEKDAY(Table1[Call date],2)<6)*((HOUR(Table1[Call time]+Table1[Call duration])<7)+(HOUR(Table1[Call time])>19)))

See the attached spreadsheet.View attachment Tuytuy.xlsb
 
i wanted to use a vba code / macro.
i know how to do with a formula ;)
 
because it will be part of a way longer macro, i need to do this in a new file every week, so using a vba code makes it way easier to do.
 
Okay. THe most efficient way is to leverage off of Excel's worksheet functions using VBA. (Note that you can't use the Application.WorksheetFunction method to call these functions from VBA because VBA doesn't handle SUMPRODUCT very well).

So here's some approaches:

If you set up dynamic named ranges, then you could use this:
Evaluate("=SUMPRODUCT(Usage_Amount*(WEEKDAY(Call_Date,2)<6)*((HOUR(Call_Time+Call_Duration)<7)+(HOUR(Call_Time)>19)))")

...or if using Excel 2010 tables, you could use this:
Evaluate("=SUMPRODUCT(Table1[Usage amount]*(WEEKDAY(Table1[Call date],2)<6)*((HOUR(Table1[Call time]+Table1[Call duration])<7)+(HOUR(Table1[Call time])>19)))")

Or you could use VBA to write the formula to the spreadsheet itself, and then reference that cell, like this:

Sub test()
[B1].Formula = "=SUMPRODUCT(Usage_Amount*(WEEKDAY(Call_Date,2)<6)*((HOUR(Call_Time+Call_Duration)<7)+(HOUR(Call_Time)>19)))"
Debug.Print [B1]
End Sub

Using VBA to read the entire range into an array and then iterate through it in order to calculate this could take a long time, and would be needlessly complicated.
 
Last edited:
i'm really confused with your solution how do i include this in a longer code ? because i need the answer on a seperated sheet then the one where the table is. The sheet where the table is, is called Section_6 and where i want the answer is SynthesisVSD
 
Will you be using the same workbook, and just adding new data in every week to your existing table? Or will you be using an entirely new workbook each time?
 
I will be using a whole new file every week that is why i would like to use a macro. So i just have to copy the code in and the report generate itself.
 
Okay. Another quick question, and then I can work out the best way to do this. You previously said "because it will be part of a way longer macro, i need to do this in a new file every week, so using a vba code makes it way easier to do."

WHat else will you be doing with code? Or is the purpose of the code purely to do this one thing?
 
Back
Top