Results 1 to 8 of 8

Thread: Complex counting help

  1. #1
    Neophyte Mr_E_Man's Avatar
    Join Date
    Aug 2020
    Location
    S.E. Michigan
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2016

    Complex counting help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,460
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Neophyte Mr_E_Man's Avatar
    Join Date
    Aug 2020
    Location
    S.E. Michigan
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2016

    Updated sheet with manual counts

    Quote Originally Posted by AliGW View Post
    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.
    Attached Files Attached Files

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,762
    Articles
    0
    Excel Version
    365
    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?
    Attached Files Attached Files
    Last edited by p45cal; 2020-08-04 at 03:08 PM.

  5. #5
    Neophyte Mr_E_Man's Avatar
    Join Date
    Aug 2020
    Location
    S.E. Michigan
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by p45cal View Post
    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!

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,762
    Articles
    0
    Excel Version
    365
    Attached adjusted accordingly.
    Attached Files Attached Files

  7. #7
    Neophyte Mr_E_Man's Avatar
    Join Date
    Aug 2020
    Location
    S.E. Michigan
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2016

    Talking

    Quote Originally Posted by p45cal View Post
    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 Labels Sum of VGA MONITOR COUNT Sum of KEYPAD COUNT Sum of GRILL TOUCH MONITOR COUNT
    1185 12 10 1
    1187 10 8 1
    1188 12 9 1
    1191 10 8 1
    1193 11 9 1
    Grand Total 55 44 5
    SITE # VGA Monitor Count Keypad Count Grill Touch Monitor Count
    1185 12 9 1
    1187 10 8 1
    1188 12 9 1
    1191 10 8 1
    1193 11 9 1
    Total 55 43 5

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


  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,762
    Articles
    0
    Excel Version
    365
    The table in the latest file needs refreshing - I should have done that myself - sorry.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •