Solved

Need help on countif statements using VBA and excel

Posted on 2016-10-21
16
56 Views
Last Modified: 2016-10-21
Hello

Hi

I was wondering if its possible to do multiple count if statements in VBA?

I currently have data in four columns

Column A (Name) will only ever contain two users in this scenario Spare1 and Spare2 Column B (Date) Column C (Time) Column D (Duration)

Currently it looks like this

Data currently looking like
I'm hoping to achieve the following:-

What I'm hoping to achieve
I'm struggling so far with the coding, all I have got so far is

Dim n As Long
n = Application.CountIf(Range("A:A"), "spare1") And Application.CountIf(Range("D:D"), ">00:05:00")
      With Sheet1
   .Range("$J$2").value = n

Open in new window


However I get an error of 400.

Any help is greatly appreciated.
0
Comment
Question by:Legolas786
  • 8
  • 5
  • 3
16 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41853403
Hi,

pls try
n = Application.CountIf(ActiveSheet.Range("A:A"), "spare1") And Application.CountIf(ActiveSheet.Range("D:D"), ">" & TimeValue("00:05:00"))
'or
n = Application.CountIfs(ActiveSheet.Range("A:A"), "spare1", ActiveSheet.Range("D:D"), ">00:05:00")

Open in new window

Regards
0
 

Author Comment

by:Legolas786
ID: 41853416
hi RGonzo1971

That doesnt error but the result is not exactly what I'm after, as that looks at all dates when I would only want to search for the duration per date.

Hopefully the screenshots in my original post will explain what I was looking for.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41853418
then try
m = Application.CountIfs(ActiveSheet.Range("A:A"), "spare1", ActiveSheet.Range("B:B"), DateValue("01/01/2016"), ActiveSheet.Range("D:D"), ">00:05:00")

Open in new window

0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:Legolas786
ID: 41853488
Sorry i think you've misunderstood me, is this possible to do without actually hard coding the date? so asking vba to do the countifs on each date rather than having to put in 01/01/2016 as these dates could change anytime.

Also I'm not sure how i could get the code to do the same for the spare2, and to also include the first and last time?
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41853569
pls send a dummy
0
 

Author Comment

by:Legolas786
ID: 41853582
please find it attached.

Sheet1 shows how data is currently looking, Sheet2 is what i am trying to achieve.

the end result i would like on sheet1 not sheet2 but in this example i have shown it in sheet1
Dummy.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41853675
Does it have to be VBA???

Looks like you could probably get the table of results with a Pivot Table.

See attached.
Dummy.xlsx
0
 

Author Comment

by:Legolas786
ID: 41853714
hi rob

thanks for your effort

yes ideally i would like to use VBA as i have already used it up to this point of cleaning the data.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41853715
Slight amendment to the file I uploaded.

Change the formula in column E to:

=IF(D2>TIME(0,5,0),1,0)    results 1 or 0 rather than Y or N

Select the "Count of >5" field in the pivot and change Filed Settings to Sum rather than Count.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41853721
Once the Pivot is in place, you could then have a step at the end of the Clean Up routine that just refreshes the Pivot with cleansed data.
0
 

Author Comment

by:Legolas786
ID: 41853732
I added another couple rows and refreshed it but he pivot table did not update? any ideas why?

it would although be better if i could do it using formulas on the columns rather than a pivot table, not even entirely sure that is possible.  So once the data is cleaned i could add the formulas to the columns.
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41853746
The pivot is currently set to a fixed range of data so the new rows would not have been included. This can be altered in a couple of ways;

1) make the data a table and refer the pivot to the table, as the table expands or shrinks the pivot will refer to the changed data
2) make the data a dynamic Named Range; once the name is in place use the name in the pivot range and the pivot will adjust.

If the data is being constantly deleted and replaced, it is probably simpler to go with option 2.

New version attached with a named range "DATA" using OFFSET function to set the data range based on a count of column A.
Dummy.xlsx
0
 

Author Comment

by:Legolas786
ID: 41853946
hi, many thanks for your help.  The recent version I can work with.  Just one more thing and i'm not entirely sure if this will be possible at all.

in the pivot table would it be possible to add another two columns:-

time before break time after break

between hours of 12:00 and 14:00 on each day there will be a break of around 1 hour so 01:00:00.  i wish to state the last time before the break and the time after the break, is this possible?  so the final result should look like this in the pivot table (havent completed all the fields but i guess you can make out what i'm trying to achieve) Also how do you change around the order of the columns in the pivot table? i could do with the layout matching the order below.

new layout
0
 

Author Comment

by:Legolas786
ID: 41853951
Also, sorry i'm not an excel expert but i assume I can have the pivot table to the side and once the records in columns A.B.C.D and E have been updated i can run the following to refresh the pivot table?

ThisWorkbook.RefreshAll

Open in new window

0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41854102
I am going to be away from PC for a few days so won't be able to look at the next stage for a while but no doubt other people on EE will be able to help.
0
 

Author Comment

by:Legolas786
ID: 41854123
ok many thanks for your help so far, my bad i probably should have requested it together but looking at your file i just remembered i was missing the two columns.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question