Complex counting help

Mr_E_Man

New member
Joined
Aug 3, 2020
Messages
4
Reaction score
0
Points
0
Location
S.E. Michigan
Excel Version(s)
Excel 2016
HI. I am, a new user trying to figure out how to count text items in a database of thin client peripheral device details using Excel 2016. The database has about 9,000 records, I am attaching the first 50-or so as a sample. The database contains 4 columns. The first column is a Site number, the second column is the number of a thin client device at the site, (there can be up to 9 thin client devices at a site number and then the next 2 columns are the 2 VGA connection ports with a text description of the function that each of the connected VGA devices is tied to. Some cells are blank. I need to count some of the devices based on a set of 'rules' that I am outlining below.

I am able to easily count the number of connected VGA devices using this formula in column 'E' =COUNTIFS(C3:D3,"*",C3:D3,"<>GRILL"); I am looking for help in coming up with a formula to count the other items using these rules.


COUNTING RULES:
VGA MONITOR COUNT = Count 1 VGA monitor for each port that is not blank AND does not contain "GRILL"
I am using this formula for this first count: COUNTIFS(C3;D3,"*",C3:D3,"<>GRILL"). This seems to work.
KEYPAD COUNT = Count 1 keypad for each VGA Monitor but only for the first instance of "DT" within a Site #, also excluding all "FRY" stations
I need to determine a formula to count these keypads but excluding the duplicate 'DT' counts and all 'FRY' station instances.
GRILL TOUCH MONITOR COUNT = Count the first instance of "GRILL" within a Site # but exclude all other instances at that same Site #
I assume this count formula would be similar to the one for the rule above?

Please let me know if you can help me avoid having to manually go through each line item to get the counts or if you have any questions or need additional information. Many Thanks!
 

Attachments

  • Thin Client VGA Device Counts.xlsx
    12.2 KB · Views: 9
Please mock up the expected results for the first 5 sites on your list - do this manually so we can see the numbers you are expecting.
 
Updated sheet with manual counts

Please mock up the expected results for the first 5 sites on your list - do this manually so we can see the numbers you are expecting.



AliGW,

Thank you for the quick response to my question! Please find attached the updated sheet with the manual count inserted for the first 5 sites as requested.

Hopefully that can establish the pattern I am looking for.

Please let me know if you need anything else or have questions.

I appreciate your assistance.
 

Attachments

  • Thin Client VGA Device Counts v1a.xlsx
    12.4 KB · Views: 6
The attached contains a table at cell I6, which agrees with your totals except for the keypad count of the first site (1185) where I have counted the value in cell C6 (DT BDAP) as the 3rd instance of DT at that site, and you haven't. Which is correct?
You can refresh the table by right-clicking it and choosing Refresh, it's a Power Query table.
1. Are the values correct?
2. Can you use this?
 

Attachments

  • ExcelGuru10750Thin Client VGA Device Counts.xlsx
    22.6 KB · Views: 10
Last edited:
The attached contains a table at cell I6, which agrees with your totals except for the keypad count of the first site (1185) where I have counted the value in cell C6 (DT BDAP) as the 3rd instance of DT at that site, and you haven't. Which is correct?
You can refresh the table by right-clicking it and choosing Refresh, it's a Power Query table.
1. Are the values correct?
2. Can you use this?

Hi p45cal - the specific "DT" function is the only one that does not get counted for the keypad on the 2nd or 3rd reference, it is different than the "DT BDAP" or "DT BDAP 3" entries. Those functions would use a keypad to process the order off the VGA screen at those stations. This is a restaurant kitchen video configuration used to manage the order fulfillment process. A customer would place an order at either the "FC", or front counter, or they would place it at the "DT", the drive thru window. The second or third instance of the "DT" would not use a keypad since the order would be 'served' at the order assembly station before it is given to the window clerk.

I have taken a look at your table but I do not understand how you were able to create it. I am intrigued by your method but I am still baffled as to how you achieved it. I am not too familiar with the Power Query. I am only beginning to experiment with POWER Pivot and POWER Query and data models but I can see that it is very powerful for managing unstructured data. Ultimately, I will be taking the results from this data shaping exercise and using it as a table in my data model where I need to combine all the other technology counts from various sources like SCCM script files and pricing files. The exercise is designed to create a list of the HW devices at each site for the purpose of contracting HW maintenance. I need to update the data model periodically to show changes. In the past, it has been a weeks long manual process to combine all the data but I am attempting to automate it. I hope that context helps.

Thanks for your help!
 
Attached adjusted accordingly.
 

Attachments

  • ExcelGuru10750Thin Client VGA Device Counts.xlsx
    22.6 KB · Views: 8
Attached adjusted accordingly.

Hi p45cal - I think there is still some error in the logic but your suggestion of using Power Query has been invaluable. I have been researching that tool and have come up with a method to apply some of the functions in power query in combinations to set up my rules and automate my counts and then group by the Site #'s - it works very well but took some time to figure it out.

Thanks very much for getting me on the right path!

BTW, your count was very close to mine but there is still one issue in 1185 keypad count, see below - my manual count pivoted is on top, your table is copied below:
Row LabelsSum of VGA MONITOR COUNTSum of KEYPAD COUNTSum of GRILL TOUCH MONITOR COUNT
118512101
11871081
11881291
11911081
11931191
Grand Total55445
SITE #VGA Monitor CountKeypad CountGrill Touch Monitor Count
11851291
11871081
11881291
11911081
11931191
Total55435


I am working to finish my power query shaping but I am confident I am on the right track now.

:D
 
The table in the latest file needs refreshing - I should have done that myself - sorry.
 
Back
Top