Solved

Need help on countif statements using VBA and excel

Posted on 2016-10-21
16
40 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 48

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 48

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
 

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 48

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 31

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 31

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 31

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 31

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 31

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now