Sorting Data

[omer123456]

Hi,

Could use some help with sorting data in this particular format

Thanks

[mehmetcik]

Formula for G4.

Enter using Ctrl Shift Enter

=SUM(IF(MID($A$5:$A$20,2,1)=F4,1,0))

[omer123456]

Formula for G4.

Enter using Ctrl Shift Enter

=SUM(IF(MID($A$5:$A$20,2,1)=F4,1,0))

Thx for trying, what about other cells?

[omer123456]

Formula for G4.

Enter using Ctrl Shift Enter

=SUM(IF(MID($A$5:$A$20,2,1)=F4,1,0))

Can you post an excel spreadsheet as example of your formulas?

[Pete_UK]

You can put this formula in G4:

=SUMPRODUCT(--(MID($A$5:$A$20,2,1)=F4))

then copy into G6, G8 and G11.

You can use this formula in J4:

=COUNTIF($A$5:$A$20,I4)

and copy down into J5:J11.

This formula in L4:

=IF(COUNT(O4:S4)=0,"",INDEX($O$3:$S$3,MATCH(MAX(O4:S4),O4:S4,0)))

copied down to L11 will give you the first subject that is a maximum - I'm just thinking of the best way to get the other subjects.

Hope this helps (for now).

Pete

[omer123456]

You can put this formula in G4:

=SUMPRODUCT(--(MID($A$5:$A$20,2,1)=F4))

then copy into G6, G8 and G11.

You can use this formula in J4:

=COUNTIF($A$5:$A$20,I4)

and copy down into J5:J11.

This formula in L4:

=IF(COUNT(O4:S4)=0,"",INDEX($O$3:$S$3,MATCH(MAX(O4:S4),O4:S4,0)))

copied down to L11 will give you the first subject that is a maximum - I'm just thinking of the best way to get the other subjects.

Hope this helps (for now).

Pete

Thx . Will give you feedback in a day or 2...... Can you post your example spreadsheet

[omer123456]

[QUOTE=omer123456;4381239]Thx . Will give you feedback in a day or 2[/QUOTE

Can you kindly post an excel example spreadsheet?

[omer123456]

You can put this formula in G4:

=SUMPRODUCT(--(MID($A$5:$A$20,2,1)=F4))

Hope this helps (for now).

Pete

What if I didn't want mid . Example p2dV5..... This time I need V as my criteria

[omer123456]

Hi Pete_UK

Can you kindly post your spreadsheet too?

Thx

[Pete_UK]

I deleted the file without saving it. I told you what formulae to use and in which cells, so you should be able to reproduce it yourself.

Pete

[mehmetcik]

I didn't understand what you wanted with the other formulae.

But this works for Column G.

=SUM(IF(MID($A$5:$A$20,2,1)=F4,1,0))

[omer123456]

Thx for trying