# Thread: phone bill challenge (all)

1. ## 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. ## 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)

3. ## 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. ## Re: phone bill challenge (all)

Good timing, Hans!

5. ## Re: phone bill challenge (all)

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

6. ## 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. ## 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. ## 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
•