If/Countif

[Fazila]

I need a formula that compares two columns and tells me if a grade has gone up, down or stayed the same. The table I am trying to populate is similar to the one below:

A	B	C	D	E
Grade	Year 10 TA4	Year 11 TA1
9	=sum(countif(index(rawdata,,match(h1,rawdataheadings,0)),{"9+","9","-9"}))	=sum(countif(index(rawdata,,match(i1,rawdataheadings,0)),{"9+","9","-9"}))	Up	Wherever there is a grade 9+, 9 or 9- in column H I want to know if the grade in the same row as has gone up i.e. 9 becomes 9+ or -9 becomes 9, gone down or stayed the same. I have converted the + and - to numbers
			Same
			Down
8	=sum(countif(index(rawdata,,match(h1,rawdataheadings,0)),{"9+","9","-9"})) <tbody> </tbody>	=sum(countif(index(rawdata,,match(i1,rawdataheadings,0)),{"9+","9","-9"}))	Up
			Same
			Down
7	=sum(countif(index(rawdata,,match(h1,rawdataheadings,0)),{"9+","9","-9"})) <tbody> </tbody>	=sum(countif(index(rawdata,,match(i1,rawdataheadings,0)),{"9+","9","-9"}))	Up
			Same
			Down

<tbody>
</tbody>

Hope this makes sense if anyone can think of a formula that would be really useful.

Thanks

 

[Fazila]

As a workaround I have created a helper column on my raw data sheet for Up, Down and Same and used this formula:

=COUNTIFS(INDEX(RawData,,MATCH($N$1,RawDataHeadings,0)),">=8.67",INDEX(RawData,,MATCH($P$1,RawDataHeadings,0)),U9)

However, if anyone knows a way without using a helper column that would be great.

Thanks

Fazila