Countifs and results to another sheet

[Grant A]

I have a raw data file that I wish to filter this down for a KPI for data entry within time compliance.

I have been working with countifs and able to provide an Excel formula to calculate these, I am having trouble to vba code the countifs and then send the calculations to the data sheet.

I have attach a reference sheet with some of the raw data to explain this question.

The data needs to review column or 3 classes, "restricted" "short haul" and "long haul"
These are listed in “dump sheet” in column L

Each class has a KPI for data entry time limit of within 60 minutes, 120 minutes and 300 minutes respectively. The time compliance measurement is listed in column M

I wish to count the class number of entries that were completed within the entry time limits.

Then would break each class into then 5 following fields that search for the time greater then entry compliance so where I need resource within my data team.

From the calculations within the declared variables I wish to have the above data populate the raw data to the Data sheet to the next blank row.

This dump of raw data is weekly process and over the weeks the next report would just add more data to the new empty row.

I have been working with array's or countifs but not been able to find exactly what I need from forum's or the class notes. I have successfully confused myself and would greatly appreciate for some guidance to get me back on track

 

[NARAYANK991]

Hi Grant ,

Speaking for myself , I am not able to understand either your workbook or your requirement.

1. I can see that your workbook has 2 sheets named Dump Sheet and Data.

2. I can make out that Dump Sheet contains raw data , and Data will be the report tab , where the results of processing the raw data will be displayed.

3. Dump Sheet contains data in the following columns :

• Column I - unlabelled , with data
• Column J - unlabelled , with data
• Column K - labelled , with data , which in the posted sample is identical ; I do not know whether this will always be so , or is just a coincidence.
• Column L - labelled , with data
• Column M - labelled , with data
• Column N - labelled , without data
• Column O - labelled , without data

4. Data has several columns of reportage , in which I can conclude :

• Columns A , B , AA and AB - General Data
• Columns C , F , and I through N - Pertaining to Restricted
• Columns D , G , and O through T - Pertaining to Short-haul
  
• Columns E , H , and U through Z - Pertaining to Long-haul

5. Since Restricted , Short-haul and Long-haul are 3 similar categories , what ever rules will be applied to derive results for one category , similar rules will probably be applied to derive results for the other 2 categories.

Thus , if you can take the sample data you have posted , and manually derive all the possible results for any one of the above 3 categories , and explain the logic by which you derived these results from the raw data , we can formulate the Excel formulae for doing the same.

From your explanation , I do not know whether you have done this ; if you have , then it is my mistake that I have not understood ; others will , and possibly help you out. If not , then please do so.

Narayan

 

[AVK]

I think you require as below. Formula in
"C2" =COUNTIF('Dump Sheet'!$L$2:$L$35,Data!C$1)
"D2" =COUNTIF('Dump Sheet'!$L$2:$L$35,Data!D$1)
"E2" =COUNTIF('Dump Sheet'!$L$2:$L$35,Data!E$1)
"F2" =COUNTIFS('Dump Sheet'!$L$2:$L$35,"Restricted",'Dump Sheet'!$M$2:$M$35,"<>60")
"G2" =COUNTIFS('Dump Sheet'!$L$2:$L$35,"Short Haul",'Dump Sheet'!$M$2:$M$35,">60",'Dump Sheet'!$M$2:$M$35,"<=120")
"H2" =COUNTIFS('Dump Sheet'!$L$2:$L$35,"Long Haul",'Dump Sheet'!$M$2:$M$35,">=120")
Further your point i don't understand.
Can you provide with answer which & where you want.

 

[Grant A]

Narayan / Atul,

The raw sheet with unmarked columns is the file that I have edited to provide the example of data. Apology for not being so clear. The column headers I have mostly removed as this is from system sheets for confidentiality purposes.
Will upload another example which have taken most columns from both sheets out to hope to make myself a little clearer.

Column L will list 3 various classes of Restricted, Short Haul and Long Haul. Column M is the time completed

I would like a code that would compare and countif the class in column L this would search all rows (the range usually around 2000 rows), the count to display on data sheet for the class and time limit.

Count number of each class in dump sheet column L for Restricted and put that count in data sheet column C

repeat this for Short Haul to column D and Long Haul to column E

Countif column L "restricted" is true and completed <=60, count all rows and put solution to data sheet column F

Countif column L "short haul" is true and completed <=120, count all rows and put solution to data sheet column G.

Then again same for long haul to column H.

 

[AVK]

formula :
"C2" =COUNTIF('Dump Sheet'!$L:$L,Data!C$1)
"D2" =COUNTIF('Dump Sheet'!$L:$L,Data!D$1)
"E2" =COUNTIF('Dump Sheet'!$L:$L,Data!E$1)
"F2" =COUNTIFS('Dump Sheet'!$L:$L,"Restricted",'Dump Sheet'!$M:$M,"<=60")
"G2" =COUNTIFS('Dump Sheet'!$L:$L,"Short Haul",'Dump Sheet'!$M:$M,"<=120")
"H2" =COUNTIFS('Dump Sheet'!$L:$L,"Long Haul",'Dump Sheet'!$M:$M,">=120")
You have not specify column "H", hence i assume that fig is >=120. You have to change as per your requirement.

 

[Grant A]

Atul,

thank you as your advice has put me back on track. I have also recorded the macro formula for the various KPI's against the 3 different classes in Dump Sheet column L. Your assistance gave me the information to adjust these for the 18 different counts (6 against each class)
With these results now working from your suggested code to place to next row results on the data sheet.