Results with and without "s"

[cyliyu]

How to have the result without a "S" (Time) when sumproduct is equal to 1 and
with a "S" (Times) when sumproduct is => 2?

Alternative, if I want to display 1st, 2nd, 3rd, 4th and so on, is it possible?

=IF(E6 <> "", SUMPRODUCT(($E$6:$E$1050=$E6) * ($F$6:$F$1050 = $F6))&" Times", "")

 

[Hui]

=IF(E6 <> "", SUMPRODUCT(($E$6:$E$1050=$E6) * ($F$6:$F$1050 = $F6))&" Time", if(SUMPRODUCT(($E$6:$E$1050=$E6) * ($F$6:$F$1050 = $F6))>1,"s",""))

 

[Hui]

for the 1st, 2nd 3rd etc
have a read of: http://www.cpearson.com/excel/Ordinal.aspx

 

[cyliyu]

=IF(E6 <> "", SUMPRODUCT(($E$6:$E$1050=$E6) * ($F$6:$F$1050 = $F6))&" Time", if(SUMPRODUCT(($E$6:$E$1050=$E6) * ($F$6:$F$1050 = $F6))>1,"s",""))

The formula having the following output.

 

[bosco_yip]

1] for the Time or Times, H6 copied down :

=IF(E6="","",COUNTIFS($E$6:$E$1050,$E6,$F$6:$F$1050,$F6)&" Time"&IF(COUNTIFS($E$6:$E$1050,$E6,$F$6:$F$1050,$F6)>1,"s",""))

2] for the 1st, 2nd, 3rd etc, H6 copied down :

=IF(E6="","",COUNTIFS($E$6:$E$1050,$E6,$F$6:$F$1050,$F6)&MID("thstndrd"&REPT("th",16)&REPT("thstndrdthththththth",8),2*MOD(COUNTIFS($E$6:$E$1050,$E6,$F$6:$F$1050,$F6),100)+1,2))

Regards
Bosco

 

[cyliyu]

Thanks, bosco_yip.
Problem solved.

 

[bosco_yip]

1] for the Time or Times, H6 copied down :

=IF(E6="","",COUNTIFS($E$6:$E$1050,$E6,$F$6:$F$1050,$F6)&" Time"&IF(COUNTIFS($E$6:$E$1050,$E6,$F$6:$F$1050,$F6)>1,"s",""))

2] for the 1st, 2nd, 3rd etc, H6 copied down :

=IF(E6="","",COUNTIFS($E$6:$E$1050,$E6,$F$6:$F$1050,$F6)&MID("thstndrd"&REPT("th",16)&REPT("thstndrdthththththth",8),2*MOD(COUNTIFS($E$6:$E$1050,$E6,$F$6:$F$1050,$F6),100)+1,2))

Regards
Bosco

For the "Time" or "Times", the formula can be shortened to :

=IF(E6="","",TEXT(COUNTIFS(E:E,E6,F:F,F6),"[=1]0 ""Time"";0 ""Times"""))

Regards
Bosco

 

[cyliyu]

Thanks, Bosco.
Good learning from you.
Appreciated.