A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Filter and SumProduct



 
 
Thread Tools Display Modes
  #1  
Old April 18th 07, 03:24 AM posted to microsoft.public.excel.worksheet.functions
Jay
external usenet poster
 
Posts: 671
Default Filter and SumProduct

I am trying to use a SumProduct function but want to be able to do so on a
filtered set of rows. SumProduct is not one of the functions available with
SUBTOTAL. SumProduct is also not available for any of the "D" functions
associated with database calculations.

If anyone knows how to accomplish this I'd greatly appreciate your response.

Thanks!
Ads
  #2  
Old April 18th 07, 03:58 AM posted to microsoft.public.excel.worksheet.functions
JMB
external usenet poster
 
Posts: 2,062
Default Filter and SumProduct

If, for example, you want to total C2:C9 where A2:A9=jeff and B2:B9=abc, try:

=SUMPRODUCT(--(A2:A9="jeff"),--(B2:B9="abc"),SUBTOTAL(9,OFFSET(C2,ROW(C2:C9)-ROW(C2),0)))

Modify as needed.

"Jay" wrote:

> I am trying to use a SumProduct function but want to be able to do so on a
> filtered set of rows. SumProduct is not one of the functions available with
> SUBTOTAL. SumProduct is also not available for any of the "D" functions
> associated with database calculations.
>
> If anyone knows how to accomplish this I'd greatly appreciate your response.
>
> Thanks!

  #3  
Old April 18th 07, 03:34 PM posted to microsoft.public.excel.worksheet.functions
Jay
external usenet poster
 
Posts: 671
Default Filter and SumProduct

JMB,

Thank you for your input; however, my problem is not yet solved (perhaps
because I didn't ask it very well).

You solution works when you know that your are filtering on columns A and B;
however, I was hoping to use this in conjection with a range that is already
filtered using the built in filtering in Excel. In other words, is there a
similar function I could use to calculate the SumProduct of the rows that are
not hidden when a filter is applied to the range of data? Using your formula
I would have to manually change the A2:A9="jeff" and the B2:B9="abc" in order
to apply a different filter to the data.

Your formula is very helpful though, and I will continue working with it to
see if I can play around with it to achieve my desired goal.

Thank you!


"JMB" wrote:

> If, for example, you want to total C2:C9 where A2:A9=jeff and B2:B9=abc, try:
>
> =SUMPRODUCT(--(A2:A9="jeff"),--(B2:B9="abc"),SUBTOTAL(9,OFFSET(C2,ROW(C2:C9)-ROW(C2),0)))
>
> Modify as needed.
>
> "Jay" wrote:
>
> > I am trying to use a SumProduct function but want to be able to do so on a
> > filtered set of rows. SumProduct is not one of the functions available with
> > SUBTOTAL. SumProduct is also not available for any of the "D" functions
> > associated with database calculations.
> >
> > If anyone knows how to accomplish this I'd greatly appreciate your response.
> >
> > Thanks!

  #4  
Old April 18th 07, 06:20 PM posted to microsoft.public.excel.worksheet.functions
Jay
external usenet poster
 
Posts: 671
Default Filter and SumProduct

I was able to get this working with --(Offset(A2:A9="jeff",0,F1)=1) where F1
is a value representing the column I want to use in my filtering.

Thank you. Still curious about the filtered range issue if that's possible.

"JMB" wrote:

> If, for example, you want to total C2:C9 where A2:A9=jeff and B2:B9=abc, try:
>
> =SUMPRODUCT(--(A2:A9="jeff"),--(B2:B9="abc"),SUBTOTAL(9,OFFSET(C2,ROW(C2:C9)-ROW(C2),0)))
>
> Modify as needed.
>
> "Jay" wrote:
>
> > I am trying to use a SumProduct function but want to be able to do so on a
> > filtered set of rows. SumProduct is not one of the functions available with
> > SUBTOTAL. SumProduct is also not available for any of the "D" functions
> > associated with database calculations.
> >
> > If anyone knows how to accomplish this I'd greatly appreciate your response.
> >
> > Thanks!

  #5  
Old April 19th 07, 01:20 AM posted to microsoft.public.excel.worksheet.functions
JMB
external usenet poster
 
Posts: 2,062
Default Filter and SumProduct

I'll add some details, if this does not help, you should supply some example
of your data and what results you are after.

My example assumed the filter range is A2:C9, w/headers in A1:C1 and you
want to sum the visible cells in Column C where col A = jeff and col B = abc.
If col A, B, and C have numeric data and all you want is the sumproduct for
col A, B, and C for visible rows w/o any other criteria, then you can remove
the comparison criteria I added for colA and colB and just use:

=SUMPRODUCT(A2:A9,B2:B9,SUBTOTAL(9,OFFSET(C2,ROW(C 2:C9)-ROW(C2),0)))

So if you have the following data w/some rows hidden due to a filter applied
to some other column:

A B C
1 Col1 Col2 Col3
2 0 5 4
3 5 2 1 <hidden>
4 6 1 2
5 4 8 6 <hidden>
6 1 2 2 <hidden>
7 5 3 8
8 12 4 1 <hidden>
9 1 2 3


the Subtotal function returns a 0 for the hidden rows in column C, so the
array looks like:
A B C
1 Col1 Col2 Col3
2 0 5 4
3 5 2 0 <hidden>
4 6 1 2
5 4 8 0 <hidden>
6 1 2 0 <hidden>
7 5 3 8
8 12 4 0 <hidden>
9 1 2 3

Then sumproduct will do what it does and return 138. I only added the
additional constraints for example purposes as that seems to be what most
people ask for - although those constraints could be added via filters on
ColA and ColB instead of built into the formula. If there were certain items
that should always be included or excluded - I would build those constraints
into the formula so I would not have to mess w/those particular filters each
and every time I wanted to work w/ the data.

And, to give credit where it is due, kudos to Laurent Longre from whom I got
the idea (I think - it is in my notebook somewhere).


"Jay" wrote:

> JMB,
>
> Thank you for your input; however, my problem is not yet solved (perhaps
> because I didn't ask it very well).
>
> You solution works when you know that your are filtering on columns A and B;
> however, I was hoping to use this in conjection with a range that is already
> filtered using the built in filtering in Excel. In other words, is there a
> similar function I could use to calculate the SumProduct of the rows that are
> not hidden when a filter is applied to the range of data? Using your formula
> I would have to manually change the A2:A9="jeff" and the B2:B9="abc" in order
> to apply a different filter to the data.
>
> Your formula is very helpful though, and I will continue working with it to
> see if I can play around with it to achieve my desired goal.
>
> Thank you!
>
>
> "JMB" wrote:
>
> > If, for example, you want to total C2:C9 where A2:A9=jeff and B2:B9=abc, try:
> >
> > =SUMPRODUCT(--(A2:A9="jeff"),--(B2:B9="abc"),SUBTOTAL(9,OFFSET(C2,ROW(C2:C9)-ROW(C2),0)))
> >
> > Modify as needed.
> >
> > "Jay" wrote:
> >
> > > I am trying to use a SumProduct function but want to be able to do so on a
> > > filtered set of rows. SumProduct is not one of the functions available with
> > > SUBTOTAL. SumProduct is also not available for any of the "D" functions
> > > associated with database calculations.
> > >
> > > If anyone knows how to accomplish this I'd greatly appreciate your response.
> > >
> > > Thanks!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Why won't advanced filter return filter results? jaws4518 Excel Worksheet Functions 5 September 12th 06 06:11 PM
How do I use advanced filter to filter for blank cells? Monique Excel Discussion (Misc queries) 2 March 21st 06 06:43 PM
Excel auto filter doesn't recoginize case - won't filter AA from A Mikey Excel Discussion (Misc queries) 1 September 29th 05 08:18 PM
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du TC Excel Worksheet Functions 1 May 12th 05 02:06 AM
advanced filter won't allow me to filter on bracketed text (-456.2 LucianoG Excel Discussion (Misc queries) 1 December 6th 04 08:38 PM


All times are GMT +1. The time now is 02:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.