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
Last edited by John Bates; 07-06-2009 at 11:24 AM.
Reason: SOLVED
(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.
Bookmarks