Count if and sum if working days and working hours macro

tuytuy

Board Regular
Joined
Mar 28, 2013
Messages
75
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

<colgroup><col><col><col><col span="2"><col><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
i have a table like above,
i need, using a macro, to count and then sum the total amount used 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.
 
But my 2 tables will update automatically as the weeks go by. Each week 2 numbers will present, number of calls and cost of calls. Do you need more information than this?
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
the thing is that i copied only a very smell bit of the table i get, the table is about 800000 lines long, and contains way more columns and sheets then that. The bit that i pasted on the msg is only an small portion of it, it really needs to be a vba code there is no other way.
 
Upvote 0
ok - I don't think I am able to help further on this without seeing the whole table and a very clear statement of the information you wish to extract. Before I retired I analysed, in real time , a spreadsheet with 90 columns and around 4000 rows per year, each year being on a separate sheet. This was done without macros, and totally automatically.
 
Upvote 0
can you send me by pm your email adress and i can send a extact of the table, i would send the complete one but it contains sensible informations and is 145 mo big...
 
Upvote 0
I have already sent you an Email - get my address from there - it is ********@zen.co.uk

Change the names and the numbers for security purposes eg tom **** fred 33 44 55
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top