How to use COUNTIFS between multiple sheets

August 29, 2018 at 07:28:03
Specs: Windows 7
I am hoping that someone can help me identify my mistake with my COUNTIFS expression. I have an excel database with multiple tables (Tabs) and am having trouble using COUNTIFS to get accurate information. For example, in he CourtTbl, column I, is the charge information for the defendant, i.e. DUI, Domestic Battery, Assault, etc... In the CrimeHistoryTbl, Column D, is the persons offenses that they have been convicted of in the past, i.e. DUI, Domestic Battery, Assault, etc... The individual records in each table are connected via a unique ID, so John Doe, has a unique ID of UYe578 and all of the records in the CourtTbl and CrimeHistoryTbl use this unique ID. I have written a CountIFS expression to count all the domestic cases in the CourtTbl who also have a past conviction for a domestic in the CrimeHistoryTbl. The expression is as follows:

=COUNTIFS(CourtTbl!I:I,"*domestic*",CrimeHistoryTbl!D:D,"*domestic*")

I am using the "LIKE" in the expression as a result of multiple users entering information and there being some inconsistencies, i.e. DOMESTIC, domestic, Domestic, etc... I would estimate that the expression should return a number greater than 40, but is only returning 6. I have gone through by hand and have immediately found more than 6 individuals who have a CourtTbl Charge of domestic, with a past offense conviction of domestic, but cannot see how to adjust my expression to get an accurate number. I am not getting any error messages.

Your assistance is much appreciated!


See More: How to use COUNTIFS between multiple sheets

Reply ↓  Report •

#1
August 29, 2018 at 12:06:23
Your issue is two fold (sort of).

First, COUNTIFS is not case sensitive, so you wouldn't need the wildcards simply for a difference in case. The wildcards (*) will work for things like Domestic Battery and DOMESTIC ASSAULT, but it's because of the "battery" and "assault" strings, not the case of the DoMeStIc string.

Second, I can't see your data, but I'm guessing that you are not using COUNTIFS correctly for your requirements.

COUNTIFS requires that the cell references "match up" in order to be counted. In other words...

If D2 contains some form of "domestic", I2 must contain some form of "domestic" to be counted. "domestic" in D2 and I3 would not be counted using the formula as written.

So, if the data for a given ID is "shifted" up or down on either sheet and the rows that contain "domestic" don't match on both sheets, those instances of "domestic" will not be counted.

To be more exact, the corresponding cells within the referenced ranges need to contain the criteria in order to be counted. Since you used entire columns as references, then the corresponding rows are the same as the corresponding cells.. However, if you had used D5:D10 and I20:I25, then D5 and I20 must meet the criteria (same for D6 & I21, etc) since they are the corresponding cells (first, second, etc.) in each referenced range.

In your case, this would return 2:

        D           I
1
2
3    Domestic     domestic
4
5
6    DoMestiC     DOMESTIC

This would return 1:

        D           I
1
2
3   Domestic     
4                domestic
5
6   DoMestiC     DOMESTIC

I hope that makes sense.

In order to provide a specific solution, we would need to know how your data is laid out. If you are going to post example data (with all personal/confidential info removed) please click on the following How-To link and read those instructions. Thanks!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
August 29, 2018 at 12:20:21
BTW...one more thing:

You can eliminate the need to try and figure out what variation of a given entry your users might enter by using a Data Validation Drop Down list in each of the user-entry cells. Force the users to choose from a list of keywords and you won't ever have to worry about upper or lower case, spelling errors, leading or trailing spaces, irrelevant entries, etc.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
August 29, 2018 at 15:10:54
Thank you for the response, your explanation makes complete sense and your second example, "This would return 1:" is exactly why I am not getting an accurate return on the COUNTIFS Expression. In that, data in each table, regardless of unique ID's does not directly align from table to table. For example, John Doe with a unique ID of UYe578 in row 149 of the CourtTbl does not align with row 149 of the CrimeHistoryTbl. Based on the explanation you provided, this is what is causing the problem. I should of mentioned that there is usually only one record per client in the CourtTbl, (unless they have been assessed multiple times), but there are many related records in the CrimeHistoryTbl (one to many relationship). Basically, a client will have one charge (or even multiple charges) at the time we assess them (one record), but can have 40 previous convictions.

Data Validation would be a great solution to the wildcard issue and the Domestic Violence, Domestic Battery, Domestic Assault, as well as numerous other possibilities is why I use the wildcard option. Crime History information can come from jurisdictions all over the country and the only thing that we can agree on is "Domestic", after that it's all up in the air.

CourtTbl

        A  D
Row 100 1 
Row 256 2
Row 300 3 Domestic
Row 310 4 Domestic
Row 311 5
Row 400 6 Domestic

CrimeHistoryTbl:

       A  I
Row 5 1
Row 10 2
Row 90 3 Domestic
Row 93 4 Domestic
Row 95 5
Row 98 6 Domestic

Above is an example of how the data is laid out. Column A is the ID column in both tables so clients number 3, 4, & 6 all have a CourtTbl charge of Domestic, and have a past conviction of Domestic, but the data appears in the CrimeHistoryTbl on different rows. Do you have a suggestion on how I account for this in the expression?


Reply ↓  Report •

Related Solutions

#4
August 29, 2018 at 16:00:42
I don't have an answer right now, but I do have another question.

What happens if the perp has a CourtTble charge of "Domestic" and multiple past convictions for "Domestic * on the CrimeHistoryTbl?

If that is supposed to count as "1" match then I'm not sure that there is a formula that will work for that. You may need a macro or a UDF (User Defined Function, written in VBA) which I might be able to help with.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
August 30, 2018 at 05:45:26
I'm sorry that this is more complicated than I thought. To answer your question, you are correct in that if a client has a CourtTbl charge of Domestic and ANY past conviction of Domestic in the CrimeHistoryTbl my intent was to count this as 1. Basically it is an ever question; has the defendant ever been convicted of a Domestic in the past and not how many times has the defendant been convicted of domestic in the past. Although that would also be helpful information as well.

Reply ↓  Report •

#6
August 30, 2018 at 19:15:02
Still working on this...I'm close. I can work on it a bit tomorrow, but then I'm going away for the weekend, so it'll be Tuesday before I can back at it.

Just wanted to stay in touch.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
August 31, 2018 at 06:39:52
Thank you so much for continuing to work on this, I really appreciate it. I too have been trying to figure it out and the only possibility that I can come up with, and I'm not sure how to do it, is to use MATCH, INDEX and COUNTIFS together in an expression. I may be way off on this!? Thank you!!!!!!

Reply ↓  Report •

#8
August 31, 2018 at 07:40:00
Actually, I am working on a User Defined Function written in VBA. A UDF is custom function that uses what is essentially a macro. Instead of entering a built-in Excel function, e.g. =COUNTIFS(...) you would enter e.g. =CRIMECOUNT(...) which would call the VBA code, run the various instructions within the code and return a value.

I believe that I have the underlying code written, which should work if you were willing to run the macro "manually" e.g. perhaps via a button or toolbar icon, but I haven't yet turned the macro into a UDF for ease of use.

Of course, a UDF or a macro would both require anyone that uses the workbook to enable macros on their machine. If that is going to be an issue, then I won't continue down that path. Let me know.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#9
August 31, 2018 at 07:49:59
Again, thank you for your work on this. Sounds like you've come up with a solution and enabling Macros won't be a problem. Thank you!

Reply ↓  Report •

#10
August 31, 2018 at 12:16:03
I'm not going to take credit for this. I went to a real Excel expert and asked if he could come up with a formula based solution to your question.

15 minutes later he offered the following array formula. (Enter via Ctrl-Shift-Enter initially and everytime you edit it)

=SUM(IF(COUNTIFS(CourtHistoryTbl!I:I,TRIM(LEFT(SUBSTITUTE(CourtTbl!D100:D400," ",
REPT(" ",100)),100))&"*",CourtHistoryTbl!A:A,IF(ISNUMBER(SEARCH("Domestic",CourtTbl!D100:D400)),
CourtTbl!A100:A400,"|")),1,0))

You will note that "Domestic" is hard coded into the formula. You can easily change that to a cell reference and enter your search string in that cell.

I am not going to try to explain how the formula works. I understand most of it, but I'd have to spend way too much time figuring it out to the level required for me to type out an understandable break-down.

The main thing for you to test is to see that works for your various forms of each offense. It works for the example data that you posted, so that is all that I can vouch for.

One last item: It's not quick. Even with the limited amount of data in your example, there is a noticeable lag time each time the formula recalculates.

Good luck!

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#11
August 31, 2018 at 14:18:35
Wow, this looks cool and while I would love an explanation of how it works, I probably wouldn't understand it. However, and this is completely my fault, but I miss typed when I provided the example above, but I think I have accounted for this and have modified the expression as listed below. Also, I was not sure why the expression was only searching in Row D100:D400, so I modified that to include all rows in the correct columns, i.e. CourtTbl!I:I

My mistake was that the correct column in the CourtTbl for Domestic is actually "I" and not "D". But again, I think that I have correctly edited the expression for this. Further, my mistake for the correct column in the CrimeHistoryTbl for Domestic is "D" and not "I". I think I have correctly edited this as well. I am terribly sorry for my transposing the columns from one table to the next. While I do think I have edited the expression correctly, it is returning "0" and I think I know why.

Modified expression:
=SUM(IF(COUNTIFS(CrimeHistoryTbl!D:D,TRIM(LEFT(SUBSTITUTE(CourtTbl!I:I," ",REPT("",100)),100))&"*",CrimeHistoryTbl!A:A,IF(ISNUMBER(SEARCH("*Domestic*",CourtTbl!I:I)),CourtTbl!A:A,"|")),1,0))

I am not getting any error message while running the expression, that's good. But, within the expression, "ISNUMBER" I think is looking for an actual numeric value, i.e. 1, 2, 3, 4, etc.. as the ID in Column A of both tables which is it will not find. The example tables I provided above were just examples and the ID field in both table (while shared and matching between tables) is actually a uniqueID such as the one I provided for John Doe: UYe578. Additionally, with the inserted Logical formula "IF" within the expression, I am guessing that it is returning "0" because it is FALSE and not necessarily incorrect, just not a numeric value. What are your thoughts?

The only other issue I can possibly imagine is with the part of the expression below, but again I don't really understand the entirety of the expression.

("",100)),100)

I can't tell what this is referring to?

Again, thank you so much for all your help and recruiting others to assist as well. THANK YOU!!!!


Reply ↓  Report •

#12
September 11, 2018 at 05:20:18
Thanks for your help with this thus far. I have continued to try to make more sense of the expression the other person provided, but it is just out of my league. I was just wondering if you have made any progress? If it would be helpful, I could provide the entire excel workbook with identifying information removed, leaving the columns in place so that it wouldn't effect the structure. Thanks again for the assistance.

Reply ↓  Report •

#13
September 11, 2018 at 05:44:20
I apologize for not getting back to you. Things have been crazy with both my "real" job and at home.

I have sent you a private message with an email address that you can send the file to. I cannot guarantee that I will get any further than we are now, but I'll give it a shot.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question