Adjust countif range according to coulumn data

[Lux Aeterna]

Hello everyone!

It's a bit messy in my head so I'll put up an example for you.

Lets say than in the past 4 months, I had 10 tests. I would like my excel to count how many As, Bs, Cs, and Ds I had in January, how many in February etc.

However I'd like to specify the end range of numbers by myself. For January, for example, the countif range would be A6 (that's fixed) to A8. For February A9 to A10, for March A11 to A13, and for April A14 to A15.

So, I'd like my excel to modify the countif function (or any other function you think is appropriate) according to the last test number (F7:F10 range).

For example, G7 cell should have =COUNTIF(C6:C8;"A"), because F7 is 3.
If F7 was 5, I'd like the countif function to auto-change to =COUNTIF(C6:C10;"A")

Accordingly, the G8 cell should have =COUNTIF(C9:C10;"A"), because F8 is 2. And so on.

Overall, I'd like to be able to modify the countif range according to the values I manually insert in F column.

Hope I you can understand it the way I tried to describe it.

PS. If there's a better function to use, I am fine with it. But I'd like to avoid VBAs since I already use some in my file.

 

[Marcelo Branco]

Try

G7 copied across and down
=COUNTIF(INDEX($C$6:$C$15,N($F6)+1):INDEX($C$6:$C$15,$F7),G$6)

M.

 

[Snakehips]

@Lux Aeterna Another possibility if you can move 'last test number' up and leave F6 blank.

Book1
	A	B	C	D	E	F	G	H	I	J
4
5	Test Number	Type	Result			Last Test Number		Result
6	1	Maths	A				A	B	C	D
7	2	Physics	B		January	3	1	2	0	0
8	3	Language	B		February	5	0	0	1	1
9	4	Chemistry	C		March	8	1	1	0	1
10	5	Geography	D		April	9	1	0	0	0
11	6	Maths	A
12	7	Maths	D
13	8	Physics	B
14	9	Chemistry	A
15	10	Chemistry	B
16
Sheet4

Cell Formulas
Range	Formula
G7:J10	G7	=SUMPRODUCT(($A$6:$A$15>$F6)*($A$6:$A$15<=$F7)*($C$6:$C$15=G$6))

 

[Lux Aeterna]

@Marcelo Branco @Snakehips thanks both of you for your replies.

Got the thing done!