Countif with multiple criteria in the same range

[Sinon]

Hello. I have a question for which I' haven't yet found an answer. An example of my data:

*	A	B	C
Company	Policy Type	Start date
A	Home insurance
A	Car insurance
A	Tax insurance
B	Car insurance
B	Tax insurance
C	Car insurance
C	Tax insurance
D	Car insurance
D	Car insurance
E	Car insurance
F	Home insurance
F	Car insurance

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]02/10/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]02/10/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]01/08/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]02/10/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]01/08/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]03/10/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]02/08/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]03/10/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]03/10/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]07/09/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]01/08/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]01/08/2015[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Basically, I work with data concerning insurance policies and I want to know how I can count how many companies have only had insurance in 2015 and not 2014. Looking at the above table, that should be companies E and F only so I would like the result to be 2. (my actual data is far more extensive of course).
Obviously, a company can have multiple policies (i.e. car insurance, home insurance, tax insurance etc) incepting in the same year so they might appear multiple times.

I have actually managed to count these by copying the companies in a different sheet, removing duplicates, doing 2 separate countifs for every company, one looking for policies incepting in 2014 and another looking for policies incepting in 2015. This way, I got what I needed.

But I need this to be workable for a complete excel beginner. Ideally, they would put their data in the sheet and there would be some formula in another sheet which would pull through the correct number. I could get away with a pivot/powerpivot as I can instruct the end user to just refresh the table after adding data. Problem is, I have no idea how to do a calculated field for such a count.

I am an excel intermediate so I could probably be able to implement something more complex and just show the end user the more simplistic aspects. I am ok with helper columns as I plan to make everything into a table so any formulas would automatically be applied to new lines.
The solution might be simple and right under my nose but I've been thinking about this for so long that I cannot see the forest for the trees. Any help would be appreciated.

Sinon

 

[Eric W]

I managed to do this with 2 helper columns. It's not especially neat either. I'll try to think of a better version of this, or maybe someone else will jump in. This formula also assumes that you have headers in row 1 and your data starts in row 2.
In D2 put: =YEAR(C2)
In E2 put: =IF(MATCH(A2,A:A,0)<>ROW(),0,IF(AND(COUNTIFS(A:A,A2,D:D,"=2014")=0,COUNTIFS(A:A,A2,D:D,"=2015")>0),1,0))
Then copy those down the D and E columns. Put a SUM(E:E) at the bottom to get the number of companies you're looking for. You can hide these columns of course.

 

[Marcelo Branco]

Is possible to get the result with a very complex array formula, but i think you should use a helper column.

Something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Company​

[/TD]
[TD]

Policy Type​

[/TD]
[TD]

Start date​

[/TD]
[TD]

Helper​

[/TD]
[TD][/TD]
[TD]

Count​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

Home insurance​

[/TD]
[TD]

02/10/2014​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

A​

[/TD]
[TD]

Car insurance​

[/TD]
[TD]

02/10/2014​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

A​

[/TD]
[TD]

Tax insurance​

[/TD]
[TD]

01/08/2015​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

B​

[/TD]
[TD]

Car insurance​

[/TD]
[TD]

02/10/2014​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

B​

[/TD]
[TD]

Tax insurance​

[/TD]
[TD]

01/08/2015​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

C​

[/TD]
[TD]

Car insurance​

[/TD]
[TD]

03/10/2014​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

C​

[/TD]
[TD]

Tax insurance​

[/TD]
[TD]

02/08/2015​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

D​

[/TD]
[TD]

Car insurance​

[/TD]
[TD]

03/10/2014​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

D​

[/TD]
[TD]

Car insurance​

[/TD]
[TD]

03/10/2014​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

E​

[/TD]
[TD]

Car insurance​

[/TD]
[TD]

07/09/2015​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

F​

[/TD]
[TD]

Home insurance​

[/TD]
[TD]

01/08/2015​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

F​

[/TD]
[TD]

Car insurance​

[/TD]
[TD]

01/08/2015​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in D2 (helper column) copied down
=--AND(MATCH(A2,A:A,0)=ROW(A2),COUNTIFS(A:A,A2,C:C,">="&DATE(2014,1,1),C:C,"<="&DATE(2014,12,31))=0,COUNTIFS(A:A,A2,C:C,">="&DATE(2015,1,1),C:C,"<="&DATE(2015,12,31))>0)

Formula in F2
=SUM(D:D)

Hope this helps

M.

 

[Sinon]

Oh I could kiss you both. Thank you very very much. They both work wonderfully. I can't believe I didn't think about using MATCH as I use that all the time for lookups. Thanks again..and here's some cookies
https://38.media.tumblr.com/d13f9477c61e0515d5edb3039579c116/tumblr_mnlbfrkImU1rvgzkpo1_500.gif

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Company[/td][td]Policy Type[/td][td]Start date[/td][td][/td][td][/td][/tr]


[tr][td]

2​

[/td][td]A[/td][td]Home insurance[/td][td]

2/10/2014​

[/td][td][/td][td]

2​

[/td][/tr]


[tr][td]

3​

[/td][td]A[/td][td]Car insurance[/td][td]

2/10/2014​

[/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td]A[/td][td]Tax insurance[/td][td]

1/8/2015​

[/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]B[/td][td]Car insurance[/td][td]

2/10/2014​

[/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]B[/td][td]Tax insurance[/td][td]

1/8/2015​

[/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]C[/td][td]Car insurance[/td][td]

3/10/2014​

[/td][td][/td][td][/td][/tr]


[tr][td]

8​

[/td][td]C[/td][td]Tax insurance[/td][td]

2/8/2015​

[/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td]D[/td][td]Car insurance[/td][td]

3/10/2014​

[/td][td][/td][td][/td][/tr]


[tr][td]

10​

[/td][td]D[/td][td]Car insurance[/td][td]

3/10/2014​

[/td][td][/td][td][/td][/tr]


[tr][td]

11​

[/td][td]E[/td][td]Car insurance[/td][td]

7/9/2015​

[/td][td][/td][td][/td][/tr]


[tr][td]

12​

[/td][td]F[/td][td]Home insurance[/td][td]

1/8/2015​

[/td][td][/td][td][/td][/tr]


[tr][td]

13​

[/td][td]F[/td][td]Car insurance[/td][td]

1/8/2015​

[/td][td][/td][td][/td][/tr]
[/table]

E2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(IF(ISNA(MATCH(IF(YEAR(C2:C13)=2015,A2:A13),
    IF(YEAR(C2:C13)=2014,A2:A13),0)),A2:A13)<>FALSE,
    MATCH(B2:B13,B2:B13,0)),ROW(B2:B13)-ROW(B2)+1),1))