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

Hi John,

Please don't SHOUT, you'll give us a headache.

=SUMPRODUCT((Mon!$A$1:$A$20=Stat!$B5)*(ISNUMBER(Mo n!$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
...
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