subtotal on sumproduct

I have a Sumproduct formular that i got from off groups but need it to
work on filtered Data

=SUMPRODUCT((A3:A51<>"")/COUNTIF(A3:A51,A3:A51&""))

Have been trying to us SUBTOTAL(3,OFFSET(INDEX............
But am unable to to get it to work.

Could some one please show me what the formular should be.

Thanks

0
sidata (8)
10/31/2006 8:52:09 PM
excel 39879 articles. 2 followers. Follow

2 Replies
760 Views

Similar Articles

[PageSpeed] 57

have you tried SUBTOTAL? Look in the help index

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<Sidata@hotmail.com> wrote in message 
news:1162327929.909939.259340@h48g2000cwc.googlegroups.com...
>I have a Sumproduct formular that i got from off groups but need it to
> work on filtered Data
>
> =SUMPRODUCT((A3:A51<>"")/COUNTIF(A3:A51,A3:A51&""))
>
> Have been trying to us SUBTOTAL(3,OFFSET(INDEX............
> But am unable to to get it to work.
>
> Could some one please show me what the formular should be.
>
> Thanks
> 


0
dguillett1 (2487)
10/31/2006 8:59:32 PM
Do you mean the number of unique values in a filtered list? The COUNTIF
approach will not work as it counts unfiltered data as well. If A is
the list (A3:A51 in your example) this formula should work (without
array entry):

=COUNT(1/FREQUENCY(MATCH(LOOKUP(ROW(A),ROW(A)/SUBTOTAL(3,OFFSET(A,ROW(A)-MIN(ROW(A)),0,1,1))/(A<>""),A),A,0),ROW(A)-MIN(ROW(A))+1))

The LOOKUP(.) part returns only the filtered items in the list

Sidata@hotmail.com wrote:

> I have a Sumproduct formular that i got from off groups but need it to
> work on filtered Data
>
> =SUMPRODUCT((A3:A51<>"")/COUNTIF(A3:A51,A3:A51&""))
>
> Have been trying to us SUBTOTAL(3,OFFSET(INDEX............
> But am unable to to get it to work.
>
> Could some one please show me what the formular should be.
> 
> Thanks

0
11/1/2006 3:28:32 PM
Reply:

Similar Artilces:

Subtotals by page
Is there any way of getting excel to generate subtotals by page? these should update automatically when pagination changes. ...

Filter Subtotal
Is it possible to filter out a subtotal row? If I have a matrix as follows, is there a way to remove the Subtotal3 row/line? Total Labor 75.00 Overhead 50.00 Subtotal1 125.00 Training 50.00 Travel 50.00 Subtotal2 100.00 G&A 75.00 Subtotal3 75.00 <<---remove? Total All 300.00 Hopefully this came out right, sorry if it did not. Is there a way to attach an image? You can do the row visibiliy to false, You need to have an appropriate expression set he...

Subtotals?
On the Account List page I wish there were sub-totals under Favorite Accounts and Other Accounts. Anyone know of a setting or hack to do this? Future functionality? ...

subtotal, match?
Morning all. I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name eleme...

sumproduct question please
Hi everyone I have a list of names in column a - I then have data in various other columns which is copied from other worksheets (Columns B, F and J are names) I want to sum how many time a name in column a is listed in the others so have entered as: =SUMPRODUCT(($B$2:$B$500=A2)*($F$2:$F$500=A2)*($J$2:$J$500=A2)) But this is not calculating correctly - can someone pls advise what step I'm missing?? -- Thanks as always Lise Try =COUNTIF(A:A,A1)+COUNTIF(F:F,A1)+COUNTIF(J:J,A1) -- Jacob "Lise" wrote: > Hi everyone > > I have a list ...

Excel subtotal
I have a large file with a layout (main area only) as follows: Name Expense ............. Brown, John $100 Brown, John $150 Brown, John $200 Doe, Jane $110 Brown, Jane $130 Brown, Jane $210 .. .. .. I need to subtotal each person's expense (names may be different each period I do this)and copy/export the expense items and subtotals for each person to a separate worksheet to be emailed to them. There may be 30 names and 2000 lines of data. Is there a way to autonomate this process (rather than subtalling then copying each person's items to a new worksh...

Subtotaling a subtotaled spreadsheet
In excel before the 2003 edition I could subtotal a spreadsheet then subtotal again by a different column. It does not work now. All of the items are subtotaled but not in correct order. For example some of the totals calculate above the information not below. Please help! Hi Alane i use this feature extensively and have found no difference in its behaviour between any of the versions, however it is important that the data is sorted in the same categories and order that it is to be subtotalled, e.g. i have a worksheet that has region, state, department information on it and i want t...

Complicated SUMPRODUCT OR SUMIF question?
I have a spreadsheet that looks like the following: S L M S S L 1/1/06 2 3 5 4 3 2 1/2/06 1 5 3 2 7 1 1/3/06 0 4 2 3 8 1 1/4/06 4 7 5 7 9 1 1/5/06 7 1 1 7 0 8 I would like to be able to sum values for all 'S' columns between any two dates, like between 1/2/06 and 1/4/06, without including all dates in my equation. Any help would be greatly appreciated. -Thanks Assumptions: B1:G1 contains the column labels A2:A6 contains the date ...

removing subtotals
Sometimes when I add subtotals and then remove them the sheet still has the subtotal 1,2 columns on the left side of the sheet. How can I remove them. gls858 First, make sure you select the entire range (or whole worksheet) that has the subtotals. In face, I'd select all the cells and try removing them once more. But in xl2003, you can hide those outlining symbols via: tools|options|view Tab|uncheck Outline Symbols. gls858 wrote: > > Sometimes when I add subtotals and then remove them the sheet still has > the subtotal 1,2 columns on the left side of the sheet. > How can...

Project resource hours subtotals
We have a program of linked projects that use an enterprise resource pool (Project and Server 2003). In Project's Resource Usage view, hours for all project work (not only these linked projects but any project that the resource has been assigned to) is totalled by resource. The resource shows 20,000 total hours but only has 50 hours of work on this particular plan. We are looking for a way to easily segregate these hours by project. We are currently accomplishing this by adding a Project column and then deleting any line item not associated with the project in question. This...

subtotals
Hi, I'm working with Excel 2003. When doing a subtotal, frequently I do not get the options to expand or contract the totals. These are the three boxes in the upper left hand part of the sheet. Sometimes I get them, sometimes I don't. Any ideas? thx/Jerry I've never seen xl fail to put those outlining symbols in, but maybe you could try this: Tools|Options|View Tab|make sure "Outline Symbols" is checked. Jerry wrote: > > Hi, I'm working with Excel 2003. When doing a subtotal, frequently I do not > get the options to expand or contract the totals...

Sumproduct Question #2
This formula returns a value error: =SUMPRODUCT((H17:H3000="3h")*(J17:J3000)) I formated both columns correctly so they are consistent. This formula works for just the one cell. =SUMPRODUCT((H17="3H")*(J17) I don't understand why it works for one cell but not the whole spreadsheet. Is there any text in the range J17:J3000 ? On Oct 29, 10:49=A0pm, JimS <jim...@msn.com> wrote: > This formula returns a value error: > > =3DSUMPRODUCT((H17:H3000=3D"3h")*(J17:J3000)) > > I formated both columns correctly so they are consistent. > >...

Problem with 2 levels of subtotals
I have a weird situation here. I'm trying to create 2 levels of subtotals from the spreadsheet. I have no problem creating 2 levels of subtotals using Excel 2000. However, with Excel 2003 I keep on getting 'mix up' answers on the second level subtotal. The first level of subtotal works fine until I try to create the second level of subtotals. Can somebody please advice whether is there a way around this problem in Excel 2003? Thanks in advance I'm not sure what mixed up answers are, but there are a couple of things that I can think of that can go astray. #1. The ...

Subtotals Are One Off
I was using the Subtotals feature under data. Does anyone know why my subtotals would be one off? Here is what the top of my report looks like: Adj Level Week Ending Case Type IDA Stus Electronic Count 66 Paper Count 46 Concurrent Count 113 Electronic Count 61 Paper Count 38 Other Count 100 Electronic Count 33 Paper Count 14 Title II Count 48 Electronic Count 95 ...

SUMPRODUCT 11-20-09
Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary Sheet AA - I want to be able to count if (C2:C20) in Cust table (B2:B20) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" I change &qu...

Subtotals
I need to create a copy of the results from the subtotal field but when I reduce to show me only the level two totals and copy this and paste to another worksheet it copies all the hidden info as well. I tried copying to word and back into Excel but I have the same problem. Any suggestions? f5>special>visible cells -- Don Guillett SalesAid Software donaldb@281.com "Tara Keane" <TaraKeane@discussions.microsoft.com> wrote in message news:3AC221D1-5C9F-49CC-BAD3-EC84777F10D1@microsoft.com... > I need to create a copy of the results from the subtotal field but when I...

Copy subtotals
I have a worksheet with subtotals, but I'd like to take just those subtotals to another sheet for further manipulation. Any ideas? Thanks, Diane Collapse the groups and select all the subtotals. Hit <F5> Click on "Special" Click on "Visible Cells Only" Then <OK> Right click in the selection and choose "Copy" Then navigate to wherever you wish, and then "Paste". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ===========================================...

Income Subtotal Showing Wrong Subtotal
When I set up my budget in Microsoft Money 2003 deluxe & Business, it shows the subtoal for my income as more than twice the amount of the bi-monthly income amount. Why is this wrong? When I go to Bills & Deposits, i have my paycheck deposited every 2 weeks. This amount shows up correctly but for some reason the Subtotal in the budget for Income , shows more than twice this amount. ?? ...

Trouble with subtotals
I have data exported from crystal reports 9 and want to subtotal. I subtotal and it gives me the subtotals but not the groupings. Any ideas? Question answered on a post from 10/22/05. Tools\view\check outline symbols "NYbills" wrote: > I have data exported from crystal reports 9 and want to subtotal. I subtotal > and it gives me the subtotals but not the groupings. Any ideas? ...

display data as a percentage of a subtotal in excel pivot table
how can data be displayed as a percentage of a subtotal in a pivot table? for example, I can display salesperson A's 1st quarter sales as a percentage of yearly sales. What I want to do is display salesperson A's 1st quarter sales as a percentage of quarter 1 total sales. You can add columns to your data table, and calculate the quarter, and the percent of quarter total for each row. For example, with your data in cells A1:C200 -- Salesperson Date Sales Sam 1-Jan-05 200 In cell D1, type: Qtr In cell D2, type: =CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4) In ce...

How do I subtotal data and then sort these subtotals in ascending.
Hi Steve personally i would sort the data by the category on which to subtotal then subtotal it (data / subtotals) then collapse it down so i could only see the subtotal lines select them choose edit / goto / special - visible cells only copy go to a new sheet & paste then sort that list. hope this helps Cheers JulieD "Steve_Wood" <Steve_Wood@discussions.microsoft.com> wrote in message news:456CB89E-B0D5-41A7-B646-9BC71355AA3B@microsoft.com... > Just hide the details and sort the visible cells. when you show the details, you'll be amazed (I was!) that exce...

Subtotal function
Hi all. I wish to use the SUBTOTAL function, to count the number of rows when using 'Autofilter' I have this formula: =SUBTOTAL(3,B5:B300) The problem is that all cells in the range B5:B300 contain formulae, and is giving the value 296. Is there any way to edit the formula, to count only values, in B5:B300? The formula in B5 is: =IF('Tick sheet'!G5="","",'Tick sheet'!G5) this is copied down to B300, so the values in B5:B300 are either a blank cell or "a" Is this clear? Many thanks George Gee -- One way =SUMPRODUCT(--($B$5:$B$300...

Find MAX for sumproduct
Good evening... I have the formula: =SUMPRODUCT((B1:B5)*(A1:A5="baltimore")) to give the sum of numbers that match Baltimore. e.g.: A B -------------------------------- 1. New York 85 2. Baltimore 33 3. New York 38 4. New York 77 5. Baltimore 45 I want to write the formula to find the largest (MAX) number that matches Baltimore, in this case 45. Any help would be appreciated. Mike Good morning ! (It's already 9 am, 29 Apr over here <g>) Try, array-entered ...

Help in Subtotal
I have a list containing thousands of data like Product Name, Code Quantity, Rate, Value etc. Generally I use subtotal for productwise total and use sum function fo Quantity, value etc. through subtotal how i can get Code with Product name total ? I think i am not able to understand u so please go through m attachment file. thanks in advance. s kara +------------------------------------------------------------------- |Filename: example.doc |Download: http://www.excelforum.com/attachment.php?postid=2676 +---------------------------------...

What wrong with sumproduct function?
I would like to sum all numbers, which match following conditions, =SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400)) press ctrl + shift + enter but it returns zero, and I have checked it, zero should not be the result. Does anyone have any suggestions what wrong with sumproduct function? Thanks in advance for any suggestions Eric 1. Sumproduct is not an array function. Use Enter, not Ctrl-Shift-Enter. 2. You must convert false/trues to numbers. One way: =SUMPRODUCT(($B$1816:$B$2400=$A2402)*(C$1816:C$2400<0)*(C$1816:C$2400)) Regards, Fred "E...