Countif not counting

[EvonS]

Ok so let me start off by saying that I know that this is a common problem but none of the common solutions I've tried are working. I will do my best to describe the problem and I am willing to email the spreadsheet to anyone who thinks that they would be able to help.

About the Spreadsheet
'Data Fields' sheet tracks information about individual crisis situations that I review at work. For example:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Situation #[/TD]
[TD]Risk Factors[/TD]
[TD]Study Flag[/TD]
[TD]Age Ranges[/TD]
[/TR]
[TR]
[TD]DE-2018-1[/TD]
[TD]Gangs - Gang Association[/TD]
[TD]Homelessness[/TD]
[TD]12 - 17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gangs - Threatened by Gang[/TD]
[TD]Risk of Losing Housing[/TD]
[TD]30 - 39[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Suicide - Current Suicide Risk[/TD]
[TD]Recent Escalation[/TD]
[TD]0 - 5[/TD]
[/TR]
</tbody>[/TABLE]

There are over 200 of these types of entries and in my deep data analysis I am tracking what study flags and age ranges are associated with with risk factors that I choose. I do this analysis on my 'Deep Analysis' Sheet that is set up like this:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CHOOSE A RISK FACTOR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]<Risk Factor User Selects>[/TD]
[TD]#[/TD]
[TD]DE-2018-1[/TD]
[TD]DE-2018-2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]RISK FACTORS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Alcohol Use - Alcohol use in the home[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Suicide - Person current suicide risk[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Suicide - Person previous suicide risk[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Parenting - Parent child conflict[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

The formula I use in C2 is all the way over to HB100 is this:

=IF(COUNTIF(INDIRECT("'Data Fields'!$N" & C$3 & ":N" & C$3+15), $A$2)+COUNTIF(INDIRECT("'Data Fields'!$N" & C$3 & ":N" & C$3+15), $A4)=2, 1, 0)

The cell references change according to the cell. The formula basically produces a 1 or 0 depending on if it finds the risk factor in A2 as well as a risk factor found in A4 on. The formula in column B just adds all the 1s and 0s up for me. This formula actually works for me.

The Problem
Risk factors are divided in to several categories. I would like to calculate associations based on these categories. For example: Suicide is a category with several risk factors in it. The formula above only compares individual risk factors (Suicide - Person previous suicide risk vs Suicide). My solution is this formula that uses a wild card:

=IF(COUNTIF(INDIRECT("'Data Fields'!$N" & C$3 & ":N" & C$3+15), [B]"*"&$A$2&"*"[/B])+COUNTIF(INDIRECT("'Data Fields'!$N" & C$3 & ":N" & C$3+15), $A4)=2, 1, 0)

Where A2 will contain just the risk category (i.e. Suicide). The issue is that the calculations don't add up correctly compared to when I do it manually.

I really hope all this make sense and I'm completely at a loss around what to try. All the entries in the spreadsheet entered by a drop down menu and all references to the risk factors draw from the same drop down list. I did this to eliminate the possibility of any user errors like extra spaces. I've recalculated the sheet several times and double checked my formulas but I just can't seem to figure this out. Your help would really be appreciated.

Sincerely,


Evon

 

[EvonS]

Here's the link to the file https://drive.google.com/open?id=1d_BLA-Xjpf5fvJOgGBCHdtQ3solThGo5