+ Reply to Thread
Results 1 to 6 of 6

Help with a formula to count/track the number of department combinations that exist

  1. #1
    Registered User
    Join Date
    10-19-2018
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    2

    Help with a formula to count/track the number of department combinations that exist

    Hello,

    I'm looking for some help with a formula to count/track how many companies have payments from each of the different combinations of departments.

    I have two columns on my "Data"worksheet that contain that information I'm trying to working with - the other columns either contain information I don't really need or information that I've already got working correctly.
    The two concerning columns contain the company name and the other has one of three possible acronyms (X, Y or Z) which denotes a specific department of my company that is providing the money. I have sorted the entire sheet by the company names alphabetically(A to Z) and have a separate worksheet where I'm totaling up the amount of money each company is getting as well as identifying the department that it is coming from. This is the sheet where I want to count the number of companies receiving payments from the different departments (So a count for unique companies being payed by X, X&Y, X&Z, X&Y&Z, Y&Z etc..)

    The problem I'm having is that there are multiple entries for the same company, for example Company ABC may have both multiple row entries for different payments from Department X, as well as one or more payments from Department Y or Y and Z both. I'm trying to create a formula that will count just the combinations that exists, not the number of times it occurs: each company can have multiple entries from all the different departments.

    I got lost in trying to figure it out and I've come here for some help/ideas on how to do it. I added an IF statement column which checks each Row's Company Name and Department columns against the one below it and returns "duplicate" or "unique" accordingly to get one of my previous counts. From there I tried adding more columns with IF statements which returned a "Yes" or "No" based on the Department listed and then I tried working with Vlookup() and Index(Match()) to add the departments entries together if they had the same company name, but I could only get two department names to combine - with all the blank entries I wasn't able to get a third department combination working....( I'm also convinced this is needlessly complex but it was how I was trying to work through it.)

    There are not currently a lot of records entries in the workbook so scale isn't going to be an issue. I know I could use filters to count but I would like to learn how to do this with a formula. I'm hoping it's some nested CountIF statement that some guru can assist me with.

    Apologies in advance for the details I've likely left out. Hopefully it makes sense.

    Cheers.

  2. #2
    Registered User
    Join Date
    10-21-2018
    Location
    Brazil
    MS-Off Ver
    16
    Posts
    22

    Re: Help with a formula to count/track the number of department combinations that exist

    I'm also after this... Being trying to use countif function, can select multiple rows range but can't select multiple criteria entries

  3. #3
    Forum Contributor
    Join Date
    07-04-2012
    Location
    Al Khor, Qatar
    MS-Off Ver
    Excel 2013
    Posts
    101

    Re: Help with a formula to count/track the number of department combinations that exist

    The problem I'm having is that there are multiple entries for the same company, for example Company ABC may have both multiple row entries for different payments from Department X, as well as one or more payments from Department Y or Y and Z both. I'm trying to create a formula that will count just the combinations that exists, not the number of times it occurs: each company can have multiple entries from all the different departments.
    I'm also after this... Being trying to use countif function, can select multiple rows range but can't select multiple criteria entries
    I don't know if I got your explanation correctly but what you can do is to make another column for a formula where it combines your company and department.

    Since you just want to get the unique values I suggest making a pivot table and excel will automatically provide you with the unique combinations of your company and department.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Help with a formula to count/track the number of department combinations that exist

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    10-19-2018
    Location
    Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Help with a formula to count/track the number of department combinations that exist

    Here is a quick mock up of the data and what I'm trying to do.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Help with a formula to count/track the number of department combinations that exist

    Hello NorthernExcelDummy and Welcome to Excel Forum.
    This proposal employs two helper columns on the DataSheet which may be moved and/or hidden for aesthetic purposes.
    The first helper column is populated using: =IF(D2="Unique",C2,"")
    The second helper column is populated using: =IF(OR(I1="",B2<>B1),H2,IF(H2="",I1,I1&"/"&H2))
    The formulas for X/Y/Z and X/Y are similar to: =COUNTIFS(DataSheet!I2:I20,A9)
    The formulas for X/Z and Y/Z are similar to: =SUM(B9,COUNTIFS(DataSheet!I2:I20,A10))
    Note that row 17 was added to the DataSheet for testing.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to Count Frequency of Reoccurring Number Combinations
    By 2blues13 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-23-2017, 04:46 AM
  2. Count number of scheduled time off based on a department
    By BARBIEE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2015, 07:06 PM
  3. Replies: 3
    Last Post: 03-04-2015, 03:31 PM
  4. [SOLVED] How do I count number of values in row2 that doesn't exist in row1?
    By viking2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2013, 04:11 AM
  5. [SOLVED] Count number in row if another number exist
    By Reykjavik in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-04-2013, 04:16 PM
  6. Replies: 16
    Last Post: 02-24-2012, 03:08 PM
  7. Replies: 1
    Last Post: 05-25-2011, 02:37 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1