# 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

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 ```MIKEhttp://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.MIKEmessage 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
 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 ```MIKEhttp://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 ClientTblSo 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 lifea wee bit easier.MIKEhttp://www.skeptic.com/

Report •