Results 1 to 3 of 3
  1. #1
    stanlafayette
    Guest

    Need Formula (2000)

    I'm sure you've answered this one before, I tried a search but didn't find what I was looking for, or got tired of sifting. The attached worksheet is an example of what I need. The actual worksheet contains multiple names (800 +) of people who work for different managers. I need a total of dates entered for each manager. I searched for an array formula originally. attached is a small example.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Formula (2000)

    In your example workbook, enter the following formula into cell D21:

    =SUMPRODUCT(NOT(ISBLANK($C$5:$C$12))*($F$5:$F$12=C 21))

    and fill down to cell D23.

    NOT(ISBLANK($C$5:$C$12)) is 1 for non-blank cells in C5:C12, 0 for blank cells.
    $F$5:$F$12=C21 is 1 for cells equal to C21 ("Bill") in F5:F12, 0 for other names.
    SUMPRODUCT combines them; a row only contributes 1 if both conditions are 1.

    You could also use

    =SUM(NOT(ISBLANK($C$5:$C$12))*($F$5:$F$12=C21))

    entered as an array formula, i.e. confirm with Ctrl+Shift+Enter.

  3. #3
    stanlafayette
    Guest

    Re: Need Formula (2000)

    Perfect - Thankyou!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •