# Thread: Count if and sum if working days and working hours macro

1. ## Count if and sum if working days and working hours macro

Hi,
 Month GSM number Extension Group name Subgroup name Name GSM number User ref1 User Ref2 Call date Call time Call duration Destination Zone/Country/Operator Tariff Type Supplementary services Indicative tariff Usage amount Jan-13 0470/132056 Unfiled - Niet toegekend - Non attribué 19-Dec-12 10:51:51 00:00:42 31624298877 Vodafone P-B Normal Outgoing roaming calls R 0 0.203 Jan-13 0470/132056 Unfiled - Niet toegekend - Non attribué 29-Dec-12 13:10:45 00:00:45 31624298877 Vodafone P-B Réduit Outgoing roaming calls R 0 0.2175 Jan-13 0470/132056 Unfiled - Niet toegekend - Non attribué 29-Dec-12 14:16:36 00:16:15 31651275403 Vodafone P-B Réduit Outgoing roaming calls R 0 4.7122 Jan-13 0470/132056 Unfiled - Niet toegekend - Non attribué 29-Dec-12 13:27:07 00:00:30 31651275403 Vodafone P-B Réduit Outgoing roaming calls R 0 0.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.  Reply With Quote

2. 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)))  Reply With Quote

3. i wanted to use a vba code / macro.
i know how to do with a formula   Reply With Quote

4. Ah. Why do you want to use VBA?  Reply With Quote

5. 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.  Reply With Quote

6. 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.  Reply With Quote

7. 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  Reply With Quote

8. 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?  Reply With Quote

9. 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.  Reply With Quote

10. 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?  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•