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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Grade[/TD]
[TD]Year 10 TA4[/TD]
[TD]Year 11 TA1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]=sum(countif(index(rawdata,,match(h1,rawdataheadings,0)),{"9+","9","-9"}))[/TD]
[TD]=sum(countif(index(rawdata,,match(i1,rawdataheadings,0)),{"9+","9","-9"}))[/TD]
[TD]Up[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Same[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Down[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR="class: grid"]
[TD]=sum(countif(index(rawdata,,match(h1,rawdataheadings,0)),{"9+","9","-9"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=sum(countif(index(rawdata,,match(i1,rawdataheadings,0)),{"9+","9","-9"}))[/TD]
[TD]Up[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Same[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Down[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR="class: grid"]
[TD]=sum(countif(index(rawdata,,match(h1,rawdataheadings,0)),{"9+","9","-9"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=sum(countif(index(rawdata,,match(i1,rawdataheadings,0)),{"9+","9","-9"}))[/TD]
[TD]Up[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Same[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Down[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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