How to list items meeting conditions?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

OldMac77 - 07 Dec 2007 13:07 GMT

Hello,

I have a spreadsheet with many children and their club dues. (It is
not very orderly.)
Names are in range B12 to B208 and their payments are in range F12 to
F208.
How can I write a formula that would create a list of all payments
made by "Brian Gumbs" .in another part of the spreadsheet?

eg
Brian G $23
Brian G $25
Brian G $50

(Windows XP, Excel 2003)

Thank you all,

OldMac

Reply to this Message

paul.robinson@it-tallaght.ie - 07 Dec 2007 13:28 GMT

> Hello,
>
[quoted text clipped - 15 lines]
>
> OldMac

Hi
Have you tried using a Pivot Table?
I'll assume your data table has headings called Name and Payment.
Click in your table of data and do Data, Pivot Table and
Pivot Chart Report. Click Next. Click Next again. Choose where you
want the
table. In Layout drag Name to the Row area and Payment to the Data
area. Click Finish. The values in Data are the total payments broken
down by Name.
To see the data rows for Brian G, double click his total payment and
you will get a new sheet with Brian's details on.
To update the table as you add names, right click the table and click
on Back. Select the new data Range and click Finish.
regards
Paul

Reply to this Message

Bob Phillips - 07 Dec 2007 14:25 GMT

=IF(ISERROR(SMALL(IF($B$12:$B$208=$M$1,ROW($A12:$A208)-MIN(ROW($A12:$A208))+1,""),ROW($A1))),"",
INDEX($B$12:$B$208,SMALL(IF($B$12:$B$208=$M$1,ROW($A12:$A208)-MIN(ROW($A12:$A208))+1,""),ROW($A1))))

and

=IF(ISERROR(SMALL(IF($B$12:$B$208=$M$1,ROW($A12:$A208)-MIN(ROW($A12:$A208))+1,""),ROW($A1))),"",
INDEX($F$12:$F$208,SMALL(IF($B$12:$B$208=$M$1,ROW($A12:$A208)-MIN(ROW($A12:$A208))+1,""),ROW($A1))))

which are both array formulae, and should be committed with
Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Then drag-copy them down as far as you think you might need.

BTW, I assumed the target nme is in M1.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello,
>
[quoted text clipped - 15 lines]
>
> OldMac

Reply to this Message

Ray - 07 Dec 2007 16:44 GMT

Bob,

Your formulas seem 'complicated' to me .... can you explain the logic
used to get to your solution?

My first thought on this was to have the member names (ie Brian G)
listed say in col X (she said somewhere else in the sheet),
and then in column Y have this fomula:
=sumif($B$12:$B$208,X12,$F$12:$F$208)

This could then be copied down for each member .... this solution
assumes that the member names are entered identically each time (in
Col B).

br//ray

Reply to this Message

Bob Phillips - 07 Dec 2007 18:15 GMT

It's not complicated, if you understand it (ain't that always true).

The solution you put forward would get the sum total, but you said you
wanted a list of the amounts, which is what I gave you. I also used the name
in a cell, M1 in my case. You could actually do away with the first formula,
it just returns how ever many instances of the name that are there.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Bob,
>
[quoted text clipped - 11 lines]
>
> br//ray

Reply to this Message

OldMac77 - 08 Dec 2007 02:13 GMT

> =IF(ISERROR(SMALL(IF($B$12:$B$208=$M$1,ROW($A12:$A208)-MIN(ROW($A12:$A208))+1,""),ROW($A1))),"",
> INDEX($B$12:$B$208,SMALL(IF($B$12:$B$208=$M$1,ROW($A12:$A208)-MIN(ROW($A12:$A208))+1,""),ROW($A1))))
[quoted text clipped - 41 lines]
>
> > OldMac

Hello again,

Thanks Bob, your formula works. Thanks also to the others who
responded.

Best wishes,

OldMac

Reply to this Message