Count unique values with sum-frequency array? multiple countifs? or other formulas?

[dexiw]

Hi everyone,

Sorry if I am overlooking other resources on this forum regarding a similar question.

I am currently using a sum-frequency array formula to calculate how many unique police precincts:
1) have operational interventions in a given year; and
2) see a decrease in burglaries from one year to the next.

Please note the following about the police precincts:
1) it is possible that there could be smaller intervention sites in a police precinct, which could have operational or inoperable interventions at the end of each year; and
2) the # of burglaries might only be reported at the level of a police precinct (which you can think of as the largest police jurisdiction type). This means that smaller intervention sites, in certain cases, won't have any burglary data because the police force can only report for all intervention sites at the level of a police precinct.

The array formula that I have drafted thus far is counting the unique police precincts per year correctly (when that is the only criteria in the formula)... but not the # of unique precincts that see a decrease in burglaries from one year to the next. Instead, the array formula seems to look at the # of burglaries in precincts with interventions for a given year, aggregates the # of burglaries for that year, and then compares that result to the # of burglaries for the next year. However, this does provide the correct result.

I have pasted what the raw data looks like for two police precincts below.

This is the raw data, for Precinct #1:

This is the raw data, for Precinct #2:

I am pasting different versions of the array formulas (which are more or less the same from one another) below. The arrays formula looks at all the raw data provided above in order to calculate the result. The word "DCT" in each array formula below is just the name of the tab (which I'm sure that you already knew/could have surmised).

Array #1:
=SUM(--(FREQUENCY(IF((DCT!$C$2:$C$9997<>"")*(DCT!$A$2:$A$9997="CY2016")*(DCT!$E$2:$E$9997="Operational")*(DCT!$F$2:$F$9997>=0)*(DCT!$F$2:$F$9997<>"")*(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")),MATCH(DCT!$C$2:$C$9997,DCT!$C$2:$C$9997,0)),ROW(DCT!$C$2:$C$9997)-ROW(DCT!$C$2)+1)>0))
Result from Array #1 for CY2016 = 2

Array #3:
=SUM(--(FREQUENCY(IF((DCT!$C$2:$C$9997<>"")*(DCT!$A$2:$A$9997="CY2016")*(DCT!$E$2:$E$9997="Operational")*(DCT!$F$2:$F$9997>=0)*(DCT!$F$2:$F$9997<>"")*(IF(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0"),"1","0")),MATCH(DCT!$C$2:$C$9997,DCT!$C$2:$C$9997,0)),ROW(DCT!$C$2:$C$9997)-ROW(DCT!$C$2)+1)>0))
Result from Array #2 for CY2016 = 2

The CY2016 result for each array formula, based on the raw data, should be 1, not 2. This is because Precinct #2 saw its burglaries decrease from CY2015 to CY2016, whereas Precinct #1 saw its burglaries increase in the same time period.

If it is not possible to have just *one* formula that calculates the result, that's fine. Similarly, if what I need to use is a completely different formula type, please direct me in the right direction.

Thanks!

 

[J.Ty.]

Hi,

Welcome to MrExcel.
I have two questions:

1. Does the following formula

=IF(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,"Precinct #1")>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,"Precinct #1"),"Yes","No")
correctly answer whether Precinct #1 should be included in the count or not?

2. What version of Excel are you using? In particular, does it support UNIQUE function?


Best,
J.Ty.

 

[dexiw]

Hi,

Welcome to MrExcel.
I have two questions:

1. Does the following formula

=IF(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,"Precinct #1")>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,"Precinct #1"),"Yes","No")
correctly answer whether Precinct #1 should be included in the count or not?

2. What version of Excel are you using? In particular, does it support UNIQUE function?


Best,
J.Ty.

Hi J.Ty.

Thanks for the support/questions.

1. The formula does include Precinct #1. Are you thinking that that formula, in some fashion, should be included in the array formula?
2. I have Excel 2016 and am using version 1908. It does not support the UNIQUE function, unfortunately, from what I can see.

Thanks again.

 

[J.Ty.]

Let us assume for the moment that you have a list of all Precincts, located in column A of a new worksheet.
Then the solution is as follows, and is based on the formula from my previous post:

Book1
	A	B	C
1	Precinct #1	No	0
2	Precinct #2	No
3	Precinct #3	No
Analysis

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(B1:B3,"Yes")
B1:B3	B1	=IF(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,A1)>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,A1),"Yes","No")

Now, about the list. It can be created in Excel 2016 in the following way: copy column C of your DCT worksheet and paste it into a new worksheet. Keep it selected and go to Ribbon->Data->Remove duplicates. It will create the list you need. Please tell me if this solves your problem.

If you cannot or do not want to rely on this manual method, I can tell you another, more complex solution to compute what you need.

J.Ty.

 

[dexiw]

Let us assume for the moment that you have a list of all Precincts, located in column A of a new worksheet.
Then the solution is as follows, and is based on the formula from my previous post:

Book1
	A	B	C
1	Precinct #1	No	0
2	Precinct #2	No
3	Precinct #3	No
Analysis

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(B1:B3,"Yes")
B1:B3	B1	=IF(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,A1)>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,A1),"Yes","No")

Now, about the list. It can be created in Excel 2016 in the following way: copy column C of your DCT worksheet and paste it into a new worksheet. Keep it selected and go to Ribbon->Data->Remove duplicates. It will create the list you need. Please tell me if this solves your problem.

If you cannot or do not want to rely on this manual method, I can tell you another, more complex solution to compute what you need.

J.Ty.

Hi J.Ty.

Thanks for that.

To my understanding, most of the precincts stay fairly constant over time and across different localities. However, I am wary of using a list when there could be unforeseen changes due to political demands/pressure to change any of the precincts in their current form from one year to the next.

In that case, could you provide a more complex recommendation for the formula?

Thanks again.

 

[J.Ty.]

In this case you can automatically determine in each row if you have seen that Precinct already. You can do it at as shown below. The additional column can be added to the right of your raw data. Then you compute the burglaries only for the first occurrence of each Precinct.

Book1
	A	B	C	D
1	Precinct #8	1	No	0
2	Precinct #7	1	No
3	Precinct #1	1	No
4	Precinct #4	1	No
5	Precinct #8	2
6	Precinct #10	1	No
7	Precinct #6	1	No
8	Precinct #1	2
9	Precinct #10	2
10	Precinct #1	3
11	Precinct #9	1	No
Analysis

Cell Formulas
Range	Formula
B1, B2:B11	B1	=COUNTIF($A$1:$A1,A1)
C1, C2:C11	C1	=IF(B1=1,IF(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,A1)>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$C$2:$C$9997,A1),"Yes","No"),"")
D1	D1	=COUNTIF(C1:C3,"Yes")

It might be slow, but my Excel is different and I do not have the data, so my tests are not likely to demonstrate how it behaves on your machine. I can think of a method to make it faster.

J.Ty.

 

[J.Ty.]

Sorry, COUNTIF in D1 should apply to C1:C9997.

J.Ty.

 

[dexiw]

Hi J.Ty.

Thanks for the information.

The problem that I am encountering with your suggestion (if I am understanding its implementation correctly) is that, if I were to use your formula from Column C with my own data and modify/paste it in another column for years 2016 and 2017, the results are incorrect. For example, when I modified your formula in Column C for my data but for years 2016 and 2017, the results in that new column were the exact same as the results for 2015 and 2016 (similar to your Column B above).

My suspicion (and correct me if I'm wrong) is that the formula in Column B also needs to test whether the years are {"Year 1" or "Year 2"}. If the variable Years was Column E in your table above, perhaps modifying the formula to something like the following (array formula) would help: {=SUM(COUNTIFS($A$1:$A1,A1,$E1,{"CY2015","CY2016"}))}. However, while the first cell with this modified array formula showed the correct result of 1, I got a value error for each cell after the first row. I'm not quite sure why the above modified array formula didn't work.

Any thoughts/recommendations you have to modify the formula for Column B (and/or Column C) would be appreciated.

 

[J.Ty.]

How is your data organized? Do you have a common worksheet with all historical data in it, identified by "CY20XX" tags?

J.Ty.

 

[dexiw]

Hi J.Ty.

Yes, all data will be in the same tab together. There will be a Years variable with the specific year per row/precinct.