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.
Bookmarks