?
Solved

Need help on countif statements using VBA and excel

Posted on 2016-10-21
16
Medium Priority
?
80 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
16 Comments
 
LVL 53

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 53

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 53

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 33

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 33

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 33

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 33

Accepted Solution

by:
Rob Henson earned 2000 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 33

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

650 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