Countif(s) or similar using data from different tables

[hphillips]

Hi all,

Hopefully someone can help me with this because it's driving me mad.

I have two tables on separate sheets. The first holds a list of cases and who managed the case (and how long it was open, but that one is less relevant for now). The second has a list of staff that appear in the first table and their location (UK/US).

e.g. Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Case Number[/TD]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Harry[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]UK[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]US[/TD]
[/TR]
</tbody>[/TABLE]


What I want to be able to get is the number of cases in each location - and ideally I'd just be able to drop fresh data in my table of cases and have the totals 'magically' recalculate.

Obviously I can do it quite simply if I add a VLookup to the location on the first table and then just use CountIf, but I'd like to avoid this step if possible as once I've set it up this will be handed over to other people to manage.

Are there any solutions out there?

Thanks.

 

[FormR]

Hi, welcome to the board!

Here is one option you could try:

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Case	Owner		Name	Location		Loc	Count
2	1	Jane		Harry	UK		UK	2
3	2	Joe		Jane	UK		US	3
4	3	Sarah		Joe	US
5	4	Sarah		Sarah	US
6	5	Harry
Sheet2

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(--ISNUMBER(MATCH($B$2:$B$6&"|"&G2,$D$2:$D$5&"|"&$E$2:$E$5,0)))

 

[wideboydixon]

You should be able to work this backwards from the Name/Location table:

Book1
	A	B
1	Case Number	Owner
2	1	Jane
3	2	Joe
4	3	Sarah
5	4	Sarah
6	5	Harry
7
8	Name	Location
9	Harry	UK
10	Jane	UK
11	Joe	US
12	Sarah	US
13
14	UK	2
15	US	3
Sheet4

Cell Formulas
Range	Formula
B14	{=SUM(IF($B$9:$B$12=$A14,COUNTIF($B$2:$B$6,$A$9:$A$12)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[hphillips]

Thanks both of you.

Unfortunately both of these solutions only work if I put in exact cell references which would mean updating the formulas each time as we won't have the same amount of data in each report.

 

[FormR]

Unfortunately both of these solutions only work if I put in exact cell references which would mean updating the formulas each time as we won't have the same amount of data in each report.

Hi, you have several options.

1. Use tables and structured references (my preference)
2. Simply extend the ranges used by the formula's to sensible maximums
3. Use dynamic named ranges

 

[wideboydixon]

And not forgetting this option:

4. Give a clear and accurate description of your problem in your original post.

:D

WBD

 

[hphillips]

Hi, you have several options.

1. Use tables and structured references (my preference)
2. Simply extend the ranges used by the formula's to sensible maximums
3. Use dynamic named ranges

Thanks!

I've got it working with dynamic named ranges so that's something.

I'll have a play with structured references and see if I can get that working too.

 

[jkpieterse]

Make sure both tables are formatted as table. Then create a pivot table in which you point to both tables. Excel will ask you to define a relationship between both tables.