Solved

Need help on countif statements using VBA and excel

Posted on 2016-10-21
16
65 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 50

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 50

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 50

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 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

761 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