Prev: sorting numbers
Next: Time calculations over 24hrs
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" <John(a)discussions.microsoft.com> 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 numbers
Next: Time calculations over 24hrs