Converting COUNTIFS to Excel 2003

Hi,

I have the following excel formula:
=LOWER(IF(($H2&":"&$M2&":"&$N2&":"&TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000"))=":::0000","",$H2&":"&$M2&":"&$N2&":"&TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000")))

I need to convert the COUNTIFS into something that is Excel 2003 friendly. Any help would be appreciated.

Thanks,
John

To replicate COUNTIFS/SUMIFS pre2007 you're generally looking at SUMPRODUCT which is not as efficient unfortunately, ie instead of:

TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000")

you would use

TEXT(SUMPRODUCT(--($H$2:$H2=$H2),--($M$2:$M2=$M2),--($N$2:$N2=$N2)),"0000")

(note re: above I changed your absolute references - don't really make a lot of sense in the example if formula to be copied down - presumably a typo ?)

you would be IMO at least be best served concatenating H,M & N into one string so you can just use COUNTIF, ie

Z2: $H2&$M2&$N2
copied down

Then instead of SUMPRODUCT/COUNTIFS you can use

TEXT(COUNTIF($Z$2:$Z2,$Z2),"0000")

which would be on a par with COUNTIFS performance wise.