COUNTIFS not counting (not a TRIM issue)

[EvonS]

Hello Everyone,

I'm using a COUNTIF function to count the number of times a particular organization name appears

=COUNTIF(INDIRECT("'Data Fields'!$E$"&$B$2&":$E$"&$C$2),E60)

This formula counts correctly and calls the organization name from a list on a different sheet.
The output from this formula (in combination with other formulas) looks like this
[TABLE="width: 618"]
<tbody>[TR]
[TD="class: xl67, width: 265"]

---

[/TD]
[TD="class: xl68, width: 88"]Originating[/TD]
[TD="class: xl69, width: 42"][/TD]
[TD="class: xl68, width: 53"]Lead[/TD]
[TD="class: xl69, width: 50"][/TD]
[TD="class: xl70, width: 72"]Assisting[/TD]
[TD="class: xl69, width: 48"][/TD]
[/TR]
[TR]
[TD="class: xl67, width: 265"]Toronto Police Service - 51 Division[/TD]
[TD="class: xl68, width: 88"]182[/TD]
[TD="class: xl69, width: 42"]81%[/TD]
[TD="class: xl68, width: 53"]14[/TD]
[TD="class: xl69, width: 50"]6%[/TD]
[TD="class: xl70, width: 72"]76[/TD]
[TD="class: xl69, width: 48"]8%[/TD]
[/TR]
</tbody>[/TABLE]

---

However, another section of the same sheet counts the number of times an organization name appears per month. I use the following code to accomplish this:

=COUNTIFS(INDIRECT("'Data Fields'!$e$"&$B$2&":$e$"&$C$2), "="&$Q$42, INDIRECT("'Data Fields'!$b$"&$B$2&":$b$"&$C$2), ">="&DATE(2018, 1, 1),INDIRECT("'Data Fields'!$b$"&$B$2&":$b$"&$C$2), "<="&DATE(2018,1,31))

The chart looks like this:


[TABLE="width: 376"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]

---

Agency Activity By Month[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toronto Police Service - 51 Division[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Originating[/TD]
[TD]Lead[/TD]
[TD]Assisting[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]16[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]18[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]21[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD]18[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]25[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]17[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]TOTALS[/TD]
[TD]181[/TD]
[TD]14[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

---

The totals should be equal to the ones in the first chart but they are not. Both formulas call the agency name from the same agency list so I know it's not a TRIM solution. I used the "Find & Select" tool to confirm that that the numbers in the first table are indeed correct. Does anyone have any ideas?

Thanks in advance for the help.

Regards,


Evon

 

[KolGuyXcel]

What does the cells B2 & C2 contain?

Also, which one is the Column E in the Data Sheet?

 

[EvonS]

Thank you for taking the time to reply. B2 and C2 represent the cell ranges. They allow me to easily define custom ranges for the function to count. The E column represents "originating". There are different formulas for each column where I change the column reference depending on what I want to count. The formula included in my post is the foundation for the other formulas.