Formula help please

[John]

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

 

[Steve Dunn]

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.