Solved CountIF across multiple sheet matching ID's

April 9, 2018 at 07:41:11
Specs: Windows 7
I am looking for some assistance with COUNTIFS across several sheets and matching ID Numbers as a condition.

I have an Excel Database with several tables within it and I need to run some stats based on some conditions. Within the ClientTbl there is static information such as the ID, clients name, sex, Date of Birth, etc.. (outlined below). Within the CourtTbl is the clients current charge such as “Domestic Battery” and their ID which matches the ID in the ClientTbl (outlined below). I need to get a count of the number of Males charged with “Domestic Battery” and the number of Females Charged with “Domestic Battery”. I not able to do a simple CountIF because I need to match the two ID first. Any assistance you can provide would be greatly helpful as once I can figure these steps out, I can repeat these for other conditions for stat purposes.

ClientTbl (Sample Information)

     A               B                       C                      D                 E
ID	        LastName	FirstName	DateOfBirth	Sex
NH1389	Doe	                John	                12/5/1974	Male
JU345	Smith	        Terry	        2/21/1986	Male
TY6745	Johnson	        Tim	                3/1/1967	        Male
IU987	Doe	                Jane	                7/1/1978	        Female
WE345	Greer	        Nancy	        4/15/1978	Female

CourtTbl (Sample Information)

     A               B
ID	        Charge
NH1389	Domestic Battery
JU345	DUI
TY6745	Domestic Battery
IU987	Domestic Battery
WE345	Retail Theft

As you can see with the sample Data, there should be 2 Males charged with "Domestic Battery" and 1 Female charged with "Domestic Battery". Thank you in advance for your assistance.


See More: CountIF across multiple sheet matching IDs

Report •

✔ Best Answer
April 10, 2018 at 12:49:11
Sorry, should have been a bit more specific.

On the ClientTbl sheet:

In cell A9 enter the text string "DB Male"
In cell A10 enter the text string "DB Female"

In cell B9 enter the formula: =COUNTIFS(E2:E6,"Male",CourtTbl!B2:B6,"Domestic Battery")
In cell B10 enter the formula: =COUNTIFS(E2:E6,"Female",CourtTbl!B2:B6,"Domestic Battery")

Your sheet should look something like:

   A        B              C               D             E
1  ID	  LastName	FirstName	DateOfBirth	Sex
2  NH1389  Doe	        John	        12/5/1974       Male
3  JU345   Smith        Terry	        2/21/1986	Male
4  TY6745  Johnson	Tim	        3/1/1969        Male
5  IU987   Doe	        Jane	        7/1/1978        Female
6  WE345   Greer	Nancy	        4/15/1978	Female
7
8
9  DB Male    2
10 DB Female  1

DB = Domestic Battery

MIKE

http://www.skeptic.com/



#1
April 9, 2018 at 17:23:27
Sorry, I should have done a better job at formatting the tables. I hope this makes it easier to understand.

Clienttbl (Sample Data)

  A       B             C               D               E
1 ID	  LastName	FirstName	DateOfBirth	Sex
2 NH1389  Doe	        John	        12/5/1974       Male
3 JU345	  Smith	        Terry	        2/21/1986	Male
4 TY6745  Johnson	Tim	        3/1/1969        Male
5 IU987	  Doe	        Jane	        7/1/1978        Female
6 WE345	  Greer	        Nancy	        4/15/1978	Female

CourtTbl (Sample Data)

  A       B
1 ID	  Charge
2 NH1389  Domestic Battery
3 JU345	  DUI
4 TY6745  Domestic Battery
5 IU987	  Domestic Battery
6 WE345	  Retail Theft

In short, I want to count all Males that are charged with Domestic Battery and all Females that are charged with Domestic Battery. I hope it makes more sense now. I think i have finally figured out the posting tables issue. Thank you in advance for all your assistance.


Report •

#2
April 10, 2018 at 10:04:10
Something like this should do the trick:

In cell A9 enter the text string "DB Male"
In cell A10 enter the text string "DB Female"

In cell B9 enter the formula: =COUNTIFS(E2:E6,"Male",CourtTbl!B2:B6,"Domestic Battery")
In cell B10 enter the formula: =COUNTIFS(E2:E6,"Female",CourtTbl!B2:B6,"Domestic Battery")

Does that work for you?


And Excel questions are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
April 10, 2018 at 12:14:25
In looking at your formula, it seems to assume that all the data is in one table and unfortunately it is not, otherwise it would work fine.

The way the database is laid out, within the ClientTbl is the clients ID, Name, Date of Birth, sex etc... (As indicated above) Within the CourtTbl is the Client ID (and this ID is shared with all tables within the database as a unique ID for each client), charge, arrest date, etc...(As indicated Above) I need to be able to run stats (in a new sheet) that will match the client ID from one table to the next and count all those that are charged with "Domestic Battery" who are a male and all those that are charged with "Domestic Battery" that are female. Does this make sense? If all the info was in one sheet and each record was on the same row a CountIFS would work great. Unfortunately, it is not.

I have thought about querying the tables (From Microsoft Query) to create a sheet for all records and then I could use a simple CountIFS Formula. This would work fine from my computer, but would create issues for sharing the query from user to user. I am open to suggestions and thank you in advance for your assistance.


Report •

Related Solutions

#4
April 10, 2018 at 12:49:11
✔ Best Answer
Sorry, should have been a bit more specific.

On the ClientTbl sheet:

In cell A9 enter the text string "DB Male"
In cell A10 enter the text string "DB Female"

In cell B9 enter the formula: =COUNTIFS(E2:E6,"Male",CourtTbl!B2:B6,"Domestic Battery")
In cell B10 enter the formula: =COUNTIFS(E2:E6,"Female",CourtTbl!B2:B6,"Domestic Battery")

Your sheet should look something like:

   A        B              C               D             E
1  ID	  LastName	FirstName	DateOfBirth	Sex
2  NH1389  Doe	        John	        12/5/1974       Male
3  JU345   Smith        Terry	        2/21/1986	Male
4  TY6745  Johnson	Tim	        3/1/1969        Male
5  IU987   Doe	        Jane	        7/1/1978        Female
6  WE345   Greer	Nancy	        4/15/1978	Female
7
8
9  DB Male    2
10 DB Female  1

DB = Domestic Battery

MIKE

http://www.skeptic.com/


Report •

#5
April 13, 2018 at 06:11:24
Thank you very much, this works great. I guess i was making it more difficult than it needed to be. This was hugely helpful!


Report •

#6
April 13, 2018 at 07:08:39
Glad I could help.

As an alternative, that adds an additional step, you could also use a VLOOKUP()
to get the individual charges and copy them from the CourtTbl to the ClientTbl

So with your the ClientTbl sheet like:

   A        B              C               D             E
1  ID	  LastName	FirstName	DateOfBirth	Sex
2  NH1389  Doe	        John	        12/5/1974       Male
3  JU345   Smith        Terry	        2/21/1986	Male
4  TY6745  Johnson	Tim	        3/1/1969        Male
5  IU987   Doe	        Jane	        7/1/1978        Female
6  WE345   Greer	Nancy	        4/15/1978	Female
7
8
9  DB Male    2
10 DB Female  1

DB = Domestic Battery

In cell F2 enter the formula: =VLOOKUP(A2,CourtTbl!$A$2:$B$6,2,0)
drag down five rows and your sheet should now look like:

   A        B           C           D             E        F
1  ID	  LastName   FirstName	DateOfBirth   Sex
2  NH1389  Doe	     John	 12/5/1974    Male     Domestic Battery
3  JU345   Smith     Terry	 2/21/1986    Male     DUI
4  TY6745  Johnson   Tim	  3/1/1969    Male     Domestic Battery
5  IU987   Doe	     Jane	  7/1/1978    Female   Domestic Battery
6  WE345   Greer     Nancy	 4/15/1978    Female   Retail Theft
7
8
9  DB Male    2
10 DB Female  1

DB = Domestic Battery

Now simply modify the COUNTIFS() formulas in cell B9 & B10 to:

=COUNTIFS(E2:E6,"Male",F2:F6,"Domestic Battery")
=COUNTIFS(E2:E6,"Female",F2:F6,"Domestic Battery")

This way you have all your data on one sheet and it might make life
a wee bit easier.

MIKE

http://www.skeptic.com/


Report •

Ask Question