From: John on 6 May 2010 06:31 HI, HELP REQUIRED WITH FORMULA FOR EXCEL PLEASE Q: EXCEL WORKBOOK WITH TWO SHEETS, ONE TITLED MON & THE OTHER STAT. ON SHEET MON, I HAVE TWO COLUMNS (H, K) WITH A RANGE OF SAY 20 LINES EACH. ON SHEET STAT, I HAVE TWO CELLS (B5, F5) ON A LINE. I WANT 'MON' 'H' TO COMPARE WITH 'STAT' 'B5'. ON 'H' LINES THAT MATCH WITH 'B5', IF A NUMBER IS ENTERED IN THE CORRESPONDING 'MON' 'K' LINE, 'STAT' 'F5' WILL COUNT IT. EXAMPLE: 'STAT' B5 = MM 'MON' H8 & H12 = MM 'MON' K8 = 0800 & K12='ANYTHING BUT A NUMBER' RESULT ON 'STAT' F5 SHOULD BE '1' THANKS! JOHN From: Steve Dunn on 6 May 2010 06:48 Hi John, Please don't SHOUT, you'll give us a headache. =SUMPRODUCT((Mon!\$A\$1:\$A\$20=Stat!\$B5)*(ISNUMBER(Mon!\$K\$1:\$K\$20))) should work, but I'm a little bit worried that you wrote 0800 rather than 800. Is that a text entry? You might need: =SUMPRODUCT((Mon!\$A\$1:\$A\$20=Stat!\$B5)* (ISNUMBER(VALUE(Mon!\$K\$1:\$K\$20)))) and if 0800 is the start of a telephone number, e.g. "0800 1234567" you will need: =SUMPRODUCT((Mon!\$A\$1:\$A\$20=Stat!\$B5)* (ISNUMBER(VALUE(SUBSTITUTE(Mon!\$K\$1:\$K\$20," ",""))))) HTH Steve D. "John" wrote in message news:411CE718-1807-4D4B-9A50-C28493436A84(a)microsoft.com...> HI, HELP REQUIRED WITH FORMULA FOR EXCEL PLEASE > > Q: > > EXCEL WORKBOOK WITH TWO SHEETS, ONE TITLED MON & THE OTHER STAT. > ON SHEET MON, I HAVE TWO COLUMNS (H, K) WITH A RANGE OF SAY 20 LINES EACH. > > ON SHEET STAT, I HAVE TWO CELLS (B5, F5) ON A LINE. > > I WANT 'MON' 'H' TO COMPARE WITH 'STAT' 'B5'. > ON 'H' LINES THAT MATCH WITH 'B5', IF A NUMBER IS ENTERED IN THE > CORRESPONDING 'MON' 'K' LINE, 'STAT' 'F5' WILL COUNT IT. > > EXAMPLE: > 'STAT' B5 = MM > 'MON' H8 & H12 = MM > 'MON' K8 = 0800 & K12='ANYTHING BUT A NUMBER' > RESULT ON 'STAT' F5 SHOULD BE '1' > > THANKS! > JOHN >  |  Pages: 1 Prev: sorting numbersNext: Time calculations over 24hrs