Formula for counting how many times value in cells above are higher.

[Jitka]

Hi
I wonder if any of you experts could point me in the right direction please? I've tried with no success.

I have a spreadsheet with staff names across various quarters, I also have a column that indicates which quarter was first and which last. I need to compare row with first and last quarter, and count how many times the value is higher in the last quarter compared to first quarter in cells C-E.
So the data looks something like this at the moment
In the column D (for all rows that are 'last' period) I'd like the result of how many time the number was higher.
All the rows in between that are not marked first or last are to be ignored.
Each staff need to have their data counted separately.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Staff[/TD]
[TD]period [/TD]
[TD]Shoes[/TD]
[TD]Dresses[/TD]
[TD]Trousers[/TD]
[TD]Total number that matches the criteria (column C-E) having value higher in the row marked as 'last' quarter against 'first' quarter[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Poppins[/TD]
[TD]First[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Poppins[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Poppins[/TD]
[TD]Last[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Star[/TD]
[TD]First[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Star[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Star[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Star[/TD]
[TD]Last[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much.

 

[OGCV1]

Just for clarification purposes could you please input the values in column D that this set of data would give.

For example would the value of D5 be 2, as the staff member Poppins has values of Shoes and Trousers that are larger than in the first period? Then D9 would be 3 as Star has all 3 values higher?

 

[Jitka]

Hi

yes that's exactly what I need. unfortunately I can't edit the table now to add it in but yes, because Poppins increased the number on 2 occasion the result in D5 would be 2.
and the same for Star, the D9 would = 3.
Thank you

 

[OGCV1]

OK, and will the data always be in order of staff member? So all the Poppins, then all Star.
Or will it be random

 

[Jitka]

it will be in order of staff member

 

[OGCV1]

Jitka,
I added one helper column D with this formula dragged down. This makes reference to the row with the last "First" period.
="C"&ROW(INDIRECT(CELL("address",INDEX($B$2:$B2,MATCH("First",$B$2:$B2)))))&":E"&ROW(INDIRECT(CELL("address",INDEX($B$2:$B2,MATCH("First",$B$2:$B2)))))
Then this formula in the next column.
<c4:e4)),"")
<c4:e4)),"")
<c4:e4)),"")
=IF(B4="Last",SUMPRODUCT(--(INDIRECT(F4)<C4:E4)),"")
OGCV</c4:e4)),"")
</c4:e4)),"")
</c4:e4)),"")

 

[OGCV1]

That last formula should be:

IF(B4="Last",SUMPRODUCT(--(INDIRECT(F4)"<"C4:E4)),"")

Just remove the quotation marks from the "<" it wont post that bit with out them

 

[Jitka]

Hi OGCV1

Thanks very much for your time on this. I'll give it a go today. I would really like to learn basic index match formula, is there a good point to start at, do you know?
Jitka

 

[Jitka]

Hi I noticed an error on my original sheet but I think you accounted for this. I put the number or rows 1 row higher than I should have, I put in where the column markers are i.e. A,B...

sorry.

 

[Jitka]

Hi again,

I tried this out on the test sheet and also my real one but I can't get it to work. I pasted the first formula in a new column, dragged down and got C2:E2 returned instead of a value. Then I pasted the other formula in another column, tried both with the quotation marks and without and got this returned IF(B4="Last",SUMPRODUCT(--(INDIRECT(F4)<C4:E4)),"")
Not sure where I am going wrong?