Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    phone bill challenge (all)

    Should be able to do this, but more I think, the less progress I make....
    Reference the attached snippet, what formulas in columns G and H will achieve the desired result?
    I suspect it's some combination of match, index, countif, sumif and/or others, else one of those array formula jobs.
    Most of the answers will be zero: I've fixed the sample for some non-zero results.
    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: phone bill challenge (all)

    Column G

    =COUNTIF($B$4:$B$25,"=" &D4)

    Column H:

    =SUMIF($B$4:$B$25,"=" &D4,$E$4:$E$25)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: phone bill challenge (all)

    In cell G4, enter the formula

    =COUNTIF($D$4:$D$24,B4)

    and in cell H4, enter the formula

    =SUMIF($D$4:$D$24,B4,$E$4:$E$24)

    Then, select G4:H4, and fill down as far as needed. But memnum 13 and 20 have the same telnum. Is that correct?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: phone bill challenge (all)

    Good timing, Hans!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: phone bill challenge (all)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: phone bill challenge (all)

    Thanks very much, all.

    "But memnum 13 and 20 have the same telnum. Is that correct?"
    Not sure if it's correct, but we are married to each other!

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

    Re: phone bill challenge (all)

    That's fine (I sincerely hope), but it means that you really don't know which MEMBER a call is to or from, only which telephone number.

  8. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: phone bill challenge (all)

    and to make it less hard to mend when someone breaks it, I've replaced the mystery $$$$$ with named ranges - just need to put new members 'in the middle' rather than at the end!

Posting Permissions

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