EXCEL - CountIF per category

Question

I have this:

1   A        B         C
2   Country Value Valid
3   Sweden  10     0
4   Sweden  5      1
5   Sweden  1      1
6   Norway  5      1
7   Norway  5      1
8   Germany 12     1
9   Germany 2      1
10  Germany 3      1
11  Germany 1      0

I want to fill in B15 to D17 in table below with number of valid values (a 1 in column C) per country and value range:

    A         B       C     D
13  Value count         
14          0 to 3  4 to 7  above 7
15  Sweden      1     1     0
16  Norway      0     2     0
17  Germany     3     0     1

I have tried IF combined with COUNTIF but i cant figure it out.

What would the formula be for cell B15?

Answer 1

Formula you are looking for is this:

=COUNTIFS($A$3:$A$11,$B15,$C$3:$C$11,1,$B$4:$B$11,"<4")

You will just need to change last criterion to $C$3:$C$11,">3",$C$3:$C$11,"<8" to make it count only values between. Note: Germany will be 2 because value for valid in last row is 0