• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

Need help on countif statements using VBA and excel

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
Legolas786
Asked:
Legolas786
  • 8
  • 5
  • 3
1 Solution
 
Rgonzo1971Commented:
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
 
Legolas786Author Commented:
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
 
Rgonzo1971Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Legolas786Author Commented:
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
 
Rgonzo1971Commented:
pls send a dummy
0
 
Legolas786Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Legolas786Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Legolas786Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Legolas786Author Commented:
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
 
Legolas786Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Legolas786Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now