Countifs - Is there an easier way to count up data for multiple criteria?

[Smurlos]

Hi Everyone,

Just wondering if anyone might have a solution to the following problem.

I have a set of data that is exported from a system we use at work into an excel file, and the data covers 4 different departments.
The trouble is, the data source doesn't split it out by the department it's assigned to, it splits it out by team leader.

I have the names of the team leaders for each department. The issue I have is that I need to filter the data shown by department when I do my manipulation.
The department is in cell A1 and is a drop down so can be toggled between Recovery and CDR. The value of which is used to determine which team leader data is used in the calculations.

I could just do a COUNTIFS and SUMIFS in each cell for each team leader... based on an IF formula for if A1 is Recovery or A1 is CDR... but that will use a lot of processing power and a lot of my time to create all the formulae in each cell.

I wondered if there was a simpler way to do it.

So for example (here's what it's like with 2 team leaders but in reality there are actually 10 team leaders for each dept so the coding is horrific):

Andrew Saunders - Recovery
Dan Wise - Recovery
Louise Brady - CDR
Heather Prophet - CDR

Please Login or Register  to view this content.

This code needs to be replicated in 20 lines of data across 30 or 40 columns. So it's going to be quite horrendous.

Can anyone see a better way? If I was using PHP/MySQL i could simply run the query:

If($A1 = "Recovery") { 
$sql = "SELECT * FROM table WHERE TeamLeader='Andrew Saunders' OR TeamLeader='Dan Wise'"; 
Else If ($A1 = "CDR") { 
$sql = "SELECT * FROM table WHERE TeamLeader='Louise Brady' OR TeamLeader='Heather Prophet'"; 
}  

So I just wondered if there was a simpler approach in Excel for this that I'm missing?

Thanks

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[PaulM100]

Follow the instructions found in the yellow banner above and with 69 posts you probably know by now the titles rules and someone will raise it for sure.

[Smurlos]

To be fair I haven't been on here for a long time - at least since those banners were introduced - I'm just trying to get something done for work and in the past people here have been very helpful with the various projects I've managed to complete with their help...

[AliGW]

Please attach a sample workbook, as requested. We have always asked for workbooks. People here are still extremely helpful, but will often ask for extra details from you. You need to be prepared to give them. Here's the canned text we have been using for years:

Will you please attach a small 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.

Instructions (Please Read Carefully):

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 (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), 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.

[davsth]

I agree an attachment would help. Its hardr to be specific without hte information

I have no idea as to the number of rows of data, which could have an influene but the alternative is to create a lookup table of the name and department, and then create an extra column in the data to return the department

then the countif simplies as you change to department matching A1

it also makes life easier if team leaders change or get added to

[Smurlos]

I think I've managed to attach a sample workbook I've just chucked together.

So the DATA tab - imagine around 30,000 records, spread across 20 or so different team leaders...

And the TABLE tab - imagine 20-30 companies listed down the left hand side. To simplify, I've removed a part of the formula as I also have to split it into dates from 0-119 days and then 120 days+ but I have left that out of this example

Basically, when you select cell A2 (sorry I said A1 above) it changes which team leaders need to be used in the data.

So just wondered if anyone had any ideas?

Say I did have another tab called for example "TEAMS" and that had in column A the team leader name and column B the department, how would I reference this and based on the value of A2 into my formulae?

Thanks in advance, appreciate it

[davsth]

you would create an additional column on the data tab

in i2 =vlookup(E2,teams!$a$1:$b$20,2,false) if the range a1:b20 is name in column a, and department in column B

in your table in C5

=SUMIFS(DATA!G:G,DATA!F:F,$B5,DATA!H:H,"In Litigation",DATA!I:I,$B$1)

[Smurlos]

So, as there's 30,000+ records of data, and the number of records changes every time as cases are opened/closed all the time, I presume this would be a case of just updating the formula in column I and tailor it based on the number of records there are each time it's refreshed?

If I were carrying out this exercise myself each week, then I'd be fine with that... but I'm creating this for someone else to do and I'm not sure what their level of Excel knowledge is. My task was to just create a sheet that someone can paste the data into and then the system updates all the values etc. itself. I'll give it a go and see how I get on.

Thank you

[davsth]

You may wish to make the teams range too large, so formula don't need adjusting in the future if the numbers grow

The formula in column I should be able to be copied down to the bottom of the data each time

[PaulM100]

Or he can wrap it in a iferror(the vlookuup,"") and drag it down for a x number of rows. Or change the data to table.

[avk]

Use Pivot Table.