Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Autofilter with Subtotal Sumif
Hi
Small example of data Name Amount Paid Sheri $10.00 Yes Sheri $15.00 No Maureen $25.00 No Maureen $12.00 Yes If I AutoFilter the the list for Name "Sheri", how can I use the Subtotal and Sumif functions together to show an answer of $15.00? Or how can I use the Sumif or Sumproduct functions only on the visible cells? Plus is it possible to show the filtered name "Sheri" in a seperate cell? -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
#2
|
|||
|
|||
Autofilter with Subtotal Sumif
Just use
=SUBTOTAL(9,B:B) To show the filtered name, use Tome Ogilvy's great ShowFilter UDF, and add this to a cell =MID(showfilter(A1)&CHAR(SUBTOTAL(9,A2)*0+32),2,9) The UDF is Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Small example of data Name Amount Paid Sheri $10.00 Yes Sheri $15.00 No Maureen $25.00 No Maureen $12.00 Yes If I AutoFilter the the list for Name "Sheri", how can I use the Subtotal and Sumif functions together to show an answer of $15.00? Or how can I use the Sumif or Sumproduct functions only on the visible cells? Plus is it possible to show the filtered name "Sheri" in a seperate cell? -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
#3
|
|||
|
|||
Autofilter with Subtotal Sumif
Hi Bob
The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: Just use =SUBTOTAL(9,B:B) To show the filtered name, use Tome Ogilvy's great ShowFilter UDF, and add this to a cell =MID(showfilter(A1)&CHAR(SUBTOTAL(9,A2)*0+32),2,9) The UDF is Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Small example of data Name Amount Paid Sheri $10.00 Yes Sheri $15.00 No Maureen $25.00 No Maureen $12.00 Yes If I AutoFilter the the list for Name "Sheri", how can I use the Subtotal and Sumif functions together to show an answer of $15.00? Or how can I use the Sumif or Sumproduct functions only on the visible cells? Plus is it possible to show the filtered name "Sheri" in a seperate cell? -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro |
#4
|
|||
|
|||
Autofilter with Subtotal Sumif
You would put Tom Ogilvy's code on a regular module.
Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt For example, to sum cells in column E, where column D contain the value "Yes", after a filter on another column, you could use the following, where there are no blank cells in those rows in column A: =SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFSET(A2:A2 00,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Robert Christie wrote: Hi Bob The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
|
|||
|
|||
Autofilter with Subtotal Sumif
Hi Debra
Could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code. So if I filter on "Maureen" the values would reflect Maur3een's unpaid amount. I'm trying to just use the Autofilter with the one criteria and show all "Sheri" data with the unpaid amount 3 row below. Thanks you to both yourself and Bob Philips for your help. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Debra Dalgleish" wrote: You would put Tom Ogilvy's code on a regular module. Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt For example, to sum cells in column E, where column D contain the value "Yes", after a filter on another column, you could use the following, where there are no blank cells in those rows in column A: =SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFSET(A2:A2 00,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Robert Christie wrote: Hi Bob The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
|
|||
|
|||
Autofilter with Subtotal Sumif
The formula that I gave you will do exactly what you ask for. It will show
62, then when you apply the filter, it will show 25. Debra's formula will provide a subtotal of Sheri's amount filtered by Sheri, Sheri and someone else, or not at all, which is not what you originally asked for. You could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless. Try the formula I gave you, apply a filter, and then tell me it doesn't work. I am looking at an example now that is filtered by Maureen, Tom's UDF shows Maureen, the amount is 37. Lo and behold, I change the filter criteria to Sheri and it shows Sheri, and an amount of 25. Exactly what you asked for. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Debra Could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code. So if I filter on "Maureen" the values would reflect Maur3een's unpaid amount. I'm trying to just use the Autofilter with the one criteria and show all "Sheri" data with the unpaid amount 3 row below. Thanks you to both yourself and Bob Philips for your help. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Debra Dalgleish" wrote: You would put Tom Ogilvy's code on a regular module. Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt For example, to sum cells in column E, where column D contain the value "Yes", after a filter on another column, you could use the following, where there are no blank cells in those rows in column A: =SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFS ET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Robert Christie wrote: Hi Bob The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#7
|
|||
|
|||
Autofilter with Subtotal Sumif
Hi Bob
You are quiet correct your formula will give me 25 filtering by "Sheri" In my reply to your first post I included the line; I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. To clarify my aims. Which are to filter on any name. See a complete list of that name's data. See a sum of that name's No (not paid) amounts which in my example would be $15.00 for Sheri and $25.00 for Maureen. You could change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless I was hoping to use the cell value from Tom Ogilvy's formula in place of the name Sheri, Maureen or any name in Laurent Longre's formula to show amount owing. Below is what I was hoping to acheive by filtering in the name column only. Small example of filtered data Name Amount Paid Sheri $10.00 Yes Sheri $15.00 No Sheri $15.00 -- Thank you once again Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: The formula that I gave you will do exactly what you ask for. It will show 62, then when you apply the filter, it will show 25. Debra's formula will provide a subtotal of Sheri's amount filtered by Sheri, Sheri and someone else, or not at all, which is not what you originally asked for. You could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless. Try the formula I gave you, apply a filter, and then tell me it doesn't work. I am looking at an example now that is filtered by Maureen, Tom's UDF shows Maureen, the amount is 37. Lo and behold, I change the filter criteria to Sheri and it shows Sheri, and an amount of 25. Exactly what you asked for. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Debra Could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code. So if I filter on "Maureen" the values would reflect Maur3een's unpaid amount. I'm trying to just use the Autofilter with the one criteria and show all "Sheri" data with the unpaid amount 3 row below. Thanks you to both yourself and Bob Philips for your help. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Debra Dalgleish" wrote: You would put Tom Ogilvy's code on a regular module. Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt For example, to sum cells in column E, where column D contain the value "Yes", after a filter on another column, you could use the following, where there are no blank cells in those rows in column A: =SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFS ET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Robert Christie wrote: Hi Bob The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#8
|
|||
|
|||
Autofilter with Subtotal Sumif
My apologies, I read that No as number. Stupid I know, but I did.
You still do not need to pick up the value from Tom's UDF as the filter will ensure it is only Sheri, what you need is to include the No test in the subtotal. That means you need a more complex formula. This should do it =SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N o")) assuming the amounts are in column B, the Yes/No flag in column C. headings in row 1, data rows 2-20. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Bob You are quiet correct your formula will give me 25 filtering by "Sheri" In my reply to your first post I included the line; I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. To clarify my aims. Which are to filter on any name. See a complete list of that name's data. See a sum of that name's No (not paid) amounts which in my example would be $15.00 for Sheri and $25.00 for Maureen. You could change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless I was hoping to use the cell value from Tom Ogilvy's formula in place of the name Sheri, Maureen or any name in Laurent Longre's formula to show amount owing. Below is what I was hoping to acheive by filtering in the name column only. Small example of filtered data Name Amount Paid Sheri $10.00 Yes Sheri $15.00 No Sheri $15.00 -- Thank you once again Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: The formula that I gave you will do exactly what you ask for. It will show 62, then when you apply the filter, it will show 25. Debra's formula will provide a subtotal of Sheri's amount filtered by Sheri, Sheri and someone else, or not at all, which is not what you originally asked for. You could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless. Try the formula I gave you, apply a filter, and then tell me it doesn't work. I am looking at an example now that is filtered by Maureen, Tom's UDF shows Maureen, the amount is 37. Lo and behold, I change the filter criteria to Sheri and it shows Sheri, and an amount of 25. Exactly what you asked for. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Debra Could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code. So if I filter on "Maureen" the values would reflect Maur3een's unpaid amount. I'm trying to just use the Autofilter with the one criteria and show all "Sheri" data with the unpaid amount 3 row below. Thanks you to both yourself and Bob Philips for your help. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Debra Dalgleish" wrote: You would put Tom Ogilvy's code on a regular module. Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt For example, to sum cells in column E, where column D contain the value "Yes", after a filter on another column, you could use the following, where there are no blank cells in those rows in column A: =SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFS ET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Robert Christie wrote: Hi Bob The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#9
|
|||
|
|||
Autofilter with Subtotal Sumif
Bob
No apology required on your part. I really need to ask in my first post everthing I require, clearly with correct criteria/information Your formula is working just great. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: My apologies, I read that No as number. Stupid I know, but I did. You still do not need to pick up the value from Tom's UDF as the filter will ensure it is only Sheri, what you need is to include the No test in the subtotal. That means you need a more complex formula. This should do it =SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N o")) assuming the amounts are in column B, the Yes/No flag in column C. headings in row 1, data rows 2-20. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Bob You are quiet correct your formula will give me 25 filtering by "Sheri" In my reply to your first post I included the line; I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. To clarify my aims. Which are to filter on any name. See a complete list of that name's data. See a sum of that name's No (not paid) amounts which in my example would be $15.00 for Sheri and $25.00 for Maureen. You could change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless I was hoping to use the cell value from Tom Ogilvy's formula in place of the name Sheri, Maureen or any name in Laurent Longre's formula to show amount owing. Below is what I was hoping to acheive by filtering in the name column only. Small example of filtered data Name Amount Paid Sheri $10.00 Yes Sheri $15.00 No Sheri $15.00 -- Thank you once again Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: The formula that I gave you will do exactly what you ask for. It will show 62, then when you apply the filter, it will show 25. Debra's formula will provide a subtotal of Sheri's amount filtered by Sheri, Sheri and someone else, or not at all, which is not what you originally asked for. You could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless. Try the formula I gave you, apply a filter, and then tell me it doesn't work. I am looking at an example now that is filtered by Maureen, Tom's UDF shows Maureen, the amount is 37. Lo and behold, I change the filter criteria to Sheri and it shows Sheri, and an amount of 25. Exactly what you asked for. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Debra Could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code. So if I filter on "Maureen" the values would reflect Maur3een's unpaid amount. I'm trying to just use the Autofilter with the one criteria and show all "Sheri" data with the unpaid amount 3 row below. Thanks you to both yourself and Bob Philips for your help. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Debra Dalgleish" wrote: You would put Tom Ogilvy's code on a regular module. Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt For example, to sum cells in column E, where column D contain the value "Yes", after a filter on another column, you could use the following, where there are no blank cells in those rows in column A: =SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFS ET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Robert Christie wrote: Hi Bob The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#10
|
|||
|
|||
Autofilter with Subtotal Sumif
Glad we got there Aussie Bob.
Regards UK Bob "Robert Christie" wrote in message ... Bob No apology required on your part. I really need to ask in my first post everthing I require, clearly with correct criteria/information Your formula is working just great. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: My apologies, I read that No as number. Stupid I know, but I did. You still do not need to pick up the value from Tom's UDF as the filter will ensure it is only Sheri, what you need is to include the No test in the subtotal. That means you need a more complex formula. This should do it =SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N o")) assuming the amounts are in column B, the Yes/No flag in column C. headings in row 1, data rows 2-20. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Bob You are quiet correct your formula will give me 25 filtering by "Sheri" In my reply to your first post I included the line; I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. To clarify my aims. Which are to filter on any name. See a complete list of that name's data. See a sum of that name's No (not paid) amounts which in my example would be $15.00 for Sheri and $25.00 for Maureen. You could change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless I was hoping to use the cell value from Tom Ogilvy's formula in place of the name Sheri, Maureen or any name in Laurent Longre's formula to show amount owing. Below is what I was hoping to acheive by filtering in the name column only. Small example of filtered data Name Amount Paid Sheri $10.00 Yes Sheri $15.00 No Sheri $15.00 -- Thank you once again Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: The formula that I gave you will do exactly what you ask for. It will show 62, then when you apply the filter, it will show 25. Debra's formula will provide a subtotal of Sheri's amount filtered by Sheri, Sheri and someone else, or not at all, which is not what you originally asked for. You could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless. Try the formula I gave you, apply a filter, and then tell me it doesn't work. I am looking at an example now that is filtered by Maureen, Tom's UDF shows Maureen, the amount is 37. Lo and behold, I change the filter criteria to Sheri and it shows Sheri, and an amount of 25. Exactly what you asked for. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Debra Could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code. So if I filter on "Maureen" the values would reflect Maur3een's unpaid amount. I'm trying to just use the Autofilter with the one criteria and show all "Sheri" data with the unpaid amount 3 row below. Thanks you to both yourself and Bob Philips for your help. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Debra Dalgleish" wrote: You would put Tom Ogilvy's code on a regular module. Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt For example, to sum cells in column E, where column D contain the value "Yes", after a filter on another column, you could use the following, where there are no blank cells in those rows in column A: =SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFS ET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Robert Christie wrote: Hi Bob The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Autofilter with Subtotal Sumif
The formula that you provided works great. I now need to modify so it can be
used to add up data that would be equal to a wild card character. For example I want to sum data that would be equal to *Bird* where this would pull data such as Birdbath and Bluebird and just plain Bird. Right now the formula returns 0. I will be on vacation for a week so I will be unable to check out any recommendations till my return. Thanks, Mitch "Bob Phillips" wrote: Glad we got there Aussie Bob. Regards UK Bob "Robert Christie" wrote in message ... Bob No apology required on your part. I really need to ask in my first post everthing I require, clearly with correct criteria/information Your formula is working just great. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: My apologies, I read that No as number. Stupid I know, but I did. You still do not need to pick up the value from Tom's UDF as the filter will ensure it is only Sheri, what you need is to include the No test in the subtotal. That means you need a more complex formula. This should do it =SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))*(C2:C20="N o")) assuming the amounts are in column B, the Yes/No flag in column C. headings in row 1, data rows 2-20. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Bob You are quiet correct your formula will give me 25 filtering by "Sheri" In my reply to your first post I included the line; I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. To clarify my aims. Which are to filter on any name. See a complete list of that name's data. See a sum of that name's No (not paid) amounts which in my example would be $15.00 for Sheri and $25.00 for Maureen. You could change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless I was hoping to use the cell value from Tom Ogilvy's formula in place of the name Sheri, Maureen or any name in Laurent Longre's formula to show amount owing. Below is what I was hoping to acheive by filtering in the name column only. Small example of filtered data Name Amount Paid Sheri $10.00 Yes Sheri $15.00 No Sheri $15.00 -- Thank you once again Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Bob Phillips" wrote: The formula that I gave you will do exactly what you ask for. It will show 62, then when you apply the filter, it will show 25. Debra's formula will provide a subtotal of Sheri's amount filtered by Sheri, Sheri and someone else, or not at all, which is not what you originally asked for. You could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code, but it would be totally pointless. Try the formula I gave you, apply a filter, and then tell me it doesn't work. I am looking at an example now that is filtered by Maureen, Tom's UDF shows Maureen, the amount is 37. Lo and behold, I change the filter criteria to Sheri and it shows Sheri, and an amount of 25. Exactly what you asked for. -- HTH RP (remove nothere from the email address if mailing direct) "Robert Christie" wrote in message ... Hi Debra Could I change the "Sheri" part of Laurent Longre's formula to refer to the cell value from Tom Ogilvy's code. So if I filter on "Maureen" the values would reflect Maur3een's unpaid amount. I'm trying to just use the Autofilter with the one criteria and show all "Sheri" data with the unpaid amount 3 row below. Thanks you to both yourself and Bob Philips for your help. -- Thank you Regards Aussie Bob C. Using Windows XP Home + Office 2003 Pro "Debra Dalgleish" wrote: You would put Tom Ogilvy's code on a regular module. Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt For example, to sum cells in column E, where column D contain the value "Yes", after a filter on another column, you could use the following, where there are no blank cells in those rows in column A: =SUMPRODUCT(--(C2:C200="Sheri"),--(D2:D200="Yes"),(E2:E200),(SUBTOTAL(3,OFFS ET(A2:A200,ROW(A2:A200)-MIN(ROW(A2:A200)),,1)))) Robert Christie wrote: Hi Bob The =SUBTOTAL(9,B:B) would give an answer of $62.00. I need to Filter on "Sheri" to show all her data and sum only her "No" Amount. Do I place Tom Ogilvy's UDL in a Module or in the sheet? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Include Subtotal in SumIf | Excel Worksheet Functions | |||
SUMIF SUBTOTAL OR SUMPRODUCT? | Excel Worksheet Functions | |||
Subtotal on SumIf | Excel Worksheet Functions | |||
Combining SUMIF and SUBTOTAL functions | Excel Worksheet Functions | |||
Can you combined the SUMIF and SUBTOTAL functions in a formula? | Excel Worksheet Functions |