Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list items meeting conditions?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list items meeting conditions?
On Dec 7, 1:07 pm, OldMac77 wrote:
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list items meeting conditions?
=IF(ISERROR(SMALL(IF($B$12:$B$208=$M$1,ROW($A12:$A 208)-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:$A 208)-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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "OldMac77" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list items meeting conditions?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list items meeting conditions?
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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ray" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to list items meeting conditions?
On Dec 7, 10:25 am, "Bob Phillips" wrote:
=IF(ISERROR(SMALL(IF($B$12:$B$208=$M$1,ROW($A12:$A 208)-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:$A 208)-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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "OldMac77" wrote in message ... 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 Hello again, Thanks Bob, your formula works. Thanks also to the others who responded. Best wishes, OldMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Meeting 12 formatted conditions | Excel Discussion (Misc queries) | |||
sum a range meeting conditions of row & column | Excel Worksheet Functions | |||
using sumproduct on subtotals meeting certain conditions | Excel Worksheet Functions | |||
Loop through cells meeting conditions | Excel Programming | |||
Meeting two conditions before summing | Excel Worksheet Functions |