Countif/Sumif - only 1st instance

[CROY1985]

Hi all

I have some data in the following layout:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Region[/TD]
[TD]Snow[/TD]
[/TR]
[TR]
[TD]01/02/17[/TD]
[TD]Scotland[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]02/02/17[/TD]
[TD]Scotland[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]03/02/17[/TD]
[TD]Scotland[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]08/02/17[/TD]
[TD]Scotland[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]02/02/17[/TD]
[TD]Wales[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]08/02/17[/TD]
[TD]Wales[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula which will will give me the total number of unique regions which have atleast 1 snow day between 2 seperately defined dates.

So say i define the dates as: 01/02/17 - 07/02/17 - the result should be 1, 08/02/17 - 14/02/17 the result would be 2.
That is to say in the first date range, Scotland had atleast 1 snow day, but wales had 0.
In the second date range both wales and scotland had 1 snowday, so the result would be 2.

Its the unique region bit that i'm stuck at.

 

[Marcelo Branco]

You need an array formula


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

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

Region​

[/TD]
[TD]

Snow​

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

StartDate​

[/TD]
[TD]

EndDate​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

01/02/2017​

[/TD]
[TD]

Scotland​

[/TD]
[TD]

1​

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

01/02/2017​

[/TD]
[TD]

07/02/2017​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

02/02/2017​

[/TD]
[TD]

Scotland​

[/TD]
[TD]

1​

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

08/02/2017​

[/TD]
[TD]

14/02/2017​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

03/02/2017​

[/TD]
[TD]

Scotland​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

08/02/2017​

[/TD]
[TD]

Scotland​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

02/02/2017​

[/TD]
[TD]

Wales​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

08/02/2017​

[/TD]
[TD]

Wales​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

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


Array formula in G2 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$7>=E2,IF(A$2:A$7<=F2,IF(C$2:C$7=1,IF(B$2:B$7<>"",MATCH(B$2:B$7,B$2:B$7,0))))),ROW(B$2:B$7)-ROW(B$2)+1),1))
Ctrl+Shift+Enter

M.

 

[CROY1985]

You need an array formula


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

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

Region​

[/TD]
[TD]

Snow​

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

StartDate​

[/TD]
[TD]

EndDate​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

01/02/2017​

[/TD]
[TD]

Scotland​

[/TD]
[TD]

1​

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

01/02/2017​

[/TD]
[TD]

07/02/2017​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

02/02/2017​

[/TD]
[TD]

Scotland​

[/TD]
[TD]

1​

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

08/02/2017​

[/TD]
[TD]

14/02/2017​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

03/02/2017​

[/TD]
[TD]

Scotland​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

08/02/2017​

[/TD]
[TD]

Scotland​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

02/02/2017​

[/TD]
[TD]

Wales​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

08/02/2017​

[/TD]
[TD]

Wales​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

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


Array formula in G2 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$7>=E2,IF(A$2:A$7<=F2,IF(C$2:C$7=1,IF(B$2:B$7<>"",MATCH(B$2:B$7,B$2:B$7,0))))),ROW(B$2:B$7)-ROW(B$2)+1),1))
Ctrl+Shift+Enter

M.

Perfect, thank you so much.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.