COUNT Issue

chelseafan1989

New Member
Joined
Jul 22, 2013
Messages
16
Hello

As per usual you have been wonderful with my other questions and I thank you immensely!

Here's an odd one.

My data analysis system throws out a lot of data in CSV format for us once a week and we've been asked to work out something.

The shops are required to count twice a day a selection of lines. However, the system throws this out as follows:

DateStore NameCount TypeException Status
29/12/2013Store 1Standard CountCOUNTED
29/12/2013Store 1Standard CountCOUNTED
29/12/2013Store 1Standard CountCOUNTED
29/12/2013Store 1Standard CountEXPIRED
30/12/2013Store 1Standard CountCOUNTED
30/12/2013Store 1Standard CountEXPIRED
30/12/2013Store 1Standard CountEXPIRED
30/12/2013Store 1Standard CountEXPIRED
31/12/2013Store 1Standard CountCOUNTED
31/12/2013Store 1Standard CountCOUNTED
31/12/2013Store 1Standard CountCOUNTED
31/12/2013Store 1Standard CountCOUNTED

<tbody>
</tbody>

Assume the following:

* Each store only had 4 counts to do per day
* If counted correctly, status is COUNTED
* If not counted at all, status is EXPIRED
* Even if EXPIRED appears more than once, it only counts once per day (i.e. no store should have any EXPIRED counts on any days, but having more than 1 still only means they have failed one day)

How, using this mix of data, can I check how many DAYS they have missed (i.e. using the example above they have 2 days with expired counts however have missed 4 items across those 2 days).

I've tried putting this into a pivot table with no luck (although it very kindly summarizes the data for me and tells me in total how many counts were required (COUNTED + EXPIRED) and how many were completed (COUNTED). I would then use this data to tell me how many days have been successfully counted (6 - Number of days with EXPIRED).

Also, there are 4 stores so this data will be muliplied by 4 and there will be around 100 rows for each store, with some having up to 300!

Hope this helps, should you need any further information then let me know

Thanking you!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Disregard
 
Last edited:

Forum statistics

Threads
1,137,300
Messages
5,680,701
Members
419,928
Latest member
dolincasting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top