How to automatize a changing range

[a_driga]

Hello!

Actually, I am a bit fuzzy in choosing the right title. But hopefully, those who would like to help me can understand what I am trying to achieve.

I have attached an excel file, please take a look. This represents my real problem.

Sheet 1:
As you can see there are two columns, first one filled with alphabet from a to c while the second one filled with number of 1 and 0.

Sheet 2:
In sheet 2, I am trying to classify which row correspond to a, b or c
As you can see for example:
a is from row 2 to row 7
b is from row 8 to row 10 and so on..

Next is I want to know for the quantity, each for 1 and 0, for a
So I use countif, and specify the range using the knowledge of row filled with a:
COUNTIF(A2:A7,1)

The thing is I would like to automatize the change in range, because the number of row containing "a" will always change.

How can I do this? Is my way of working already correct?

Many thanks for all the upcoming advices..

[martindwilson]

use countifs
=COUNTIFS(a2:1000,"=a",b2:b1000,"=1")

[a_driga]

use countifs
=COUNTIFS(a2:1000,"=a",b2:b1000,"=1")

Hi Martin,

Yeah you are right...
But what if the situation changed..

Please take a look at the attached file..

In sheet 1:
There are 4 colums: loc, code, desc, and qty
In Sheet 2:
I would like to check from code 701 to 710 (as example)
Based on that code, in which location the item is listed..
That is when I need to change my range (in my opinion)

What do you suggest? Cause I do it manually at the moment..

[a_driga]

I think I have solved the problem..