Ignoring Autofiler Hidden Rows With SUMPRODUCT And SUBTOTAL
Feb 18, 2009
I posted a thread a while back about how to ignore hidden rows from autofilter when using formulas. http://www.excelforum.com/excel-work...ml#post2039071
The solution worked perfectly. But I have since had to use this on another sheet which is laid out a bit differently, I have tried everything to try and modify the formula to work on the new sheet but so far I have failed miserably!! I need to do the exact same thing on this sheet - filter the sheet by week and search the data for certain criteria ('Line' and 'Fail Reason') and sum up the total quantity, while ignoring autofilters hidden rows. Here is an example of the sheet, along with an attempt of mine to modify the formula, which I think I have got TOTALLY wrong!
View 5 Replies
ADVERTISEMENT
Mar 7, 2012
Any way to automatically "rank" cells, ignoring any hidden rows? I don't even really need Excel to sort for me (although I wouldn't be opposed to that), but I'd like the first non-hidden cell in column A to always say "1" and the second one to say "2" and so on.
The back story, if you need it: I have a spreadsheet that tracks sales for 2 separate brands. I've created a macro and linked it to a listbox, so that you can choose which brand you want to view and Excel will hide all rows that aren't pertaining to that brand (I'm absurdly proud of that accomplishment). The only problem is the rankings are hard-coded, so when you hide one brand the rankings no longer make sense.
Every Monday I sort the items by the most recent week's sales and rank them from there. The addition of a second brand is obviously new to my little spreadsheet!
View 1 Replies
View Related
Aug 30, 2013
I need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.
The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.
View 4 Replies
View Related
Jan 29, 2010
I'm using Excel 2003. The sumif function will not allow me to ignore hidden rows in my data set that have been autofiltered. I tried using the following formula and it worked:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(CopiedB!$I$2:$I$65000,ROW(CopiedB!$I$2:$I$65000)-ROW(CopiedB!$I$2),0,1)),--(CopiedB!$A$2:$A$65000=$A22),CopiedB!$I$2:$I$65000)
The problem I am having is that the sumproduct and offset funtions used to ignore hidden rows are considered "volatile" and force a recalculation. Moreover, I have this fomula pasted 100+ times and this dramatically slows things down even when I turn autocalculate off. Is there another way to accomplish a sumif with a user defined custom function in VB? I really need to have the ability to do a sumif that ignores hidden rows and is not dramatically slow.
View 9 Replies
View Related
Oct 13, 2005
I have got a multisheets database where both rows and columns may be
hidden according to a given criteria. Only blanks rows and columns
should be hidden.
Now I need a formula to check if there is no "valuable" data hidden.
Checking test might be:
SUBTOTAL(9,reference) = SUBTOTAL(109,reference)
View 10 Replies
View Related
Feb 27, 2007
If I have two columns of data, one for mortgage rate and one for loan size, then use a data filter to display a subset of the data, how can I calculate a weighted average on just the hidden cells? Is there any sort of criteria flag that looks for hidden cells in a range? Sort of the inverse of the SUBTOTAL function.
View 9 Replies
View Related
Mar 25, 2014
I have a linear count from 1 to 160 (J3:FM3) and I hide columns manually over time depending on a certain criteria. However, I would like to count how many columns I have left. I believe you need to use the subtotal function, but I do not understand how to use it.
View 14 Replies
View Related
Feb 26, 2008
1) I have an a list of transactions (multiple lines per day / per item - variable count of lines) that add up to form daily profit.
2) I want a count of the profitable days (i.e. where the sum of (1) above is +ve) and conversely a count of losing days (i.e. where the sum of (1) above is -ve)
3) My existing SUMPRODUCT formula "=SUMPRODUCT(((InputAcctDest=$A8)*((LEFT(InputCalc,3)="ALL")+(LEFT(InputCalc,3)="TRD"))*(MID(InputCalc,21,15)>0)))" simply produces a count of the positive numbers, not a count of the subtotal per day when such total is positive.
View 9 Replies
View Related
Nov 10, 2006
I am trying to combine a subtotal formula in sumproduct formula but end in error. Appreciate if someone can assist me with the right formula. I would like to combine subtotal in the following sumproduct formula:
=SUMPRODUCT(((C3:C9)=C14)*((G3:G9)=B15)*(H3:H9))
View 5 Replies
View Related
Oct 25, 2013
I am trying to create a weighted average which will skip any row when Column B say's "yes" and then if Column N contains, a 0, I would like that to be skipped as well. The below works for skipping any row with the word "Yes", but it still includes 0 in the weighted average. Also, let's say the Column N contains a word and 0's, how can I skip that?
=SUMPRODUCT(($B$13:$B$15="Yes")*($I$13:$I$15)*(N13:N15))/SUMIF($B$13:$B$15,"Yes",$I$13:$I$15)
View 5 Replies
View Related
Jun 19, 2014
Using Excel 2010, I am trying to do a Sumproduct formula with two criteria, one of which needs to ignore text values.
Here is the set up:
Column AColumn BColumn C
(Side)(Qty)(Price)
Buy5,51215.67
Sell119,428null
Buy24,20945.77
Sell20,05412.25
...
I'm trying to find the sumproduct of Qty * Price if the side equals "Buy" (or "Sell") but ignoring the "null" value in column C. The formula I have is =SUMPRODUCT(--($A$2:$A$20="Buy")*IF(ISNUMBER($C$2:$C$20),--($B$2:$B$20*$C$2:$C$20)))
The result in the cell is 0, but if I open the Insert Function dialog box, I see the correct value being returned.
View 5 Replies
View Related
Jun 15, 2009
I have a column with with Employee ID numbers, with anywhere from 2-5 blanks rows in between them. In another sheet I want a column that returns these ID numbers without the blank rows in between. All of the ID's begin with "N", for example:
I have:
N33333
(blank row)
(blank row)
N22222
(blank row)
(blank row)
(blank row)
N66666
I want:
N33333
N22222
N66666
View 9 Replies
View Related
Oct 8, 2008
I'm trying to craft a gradesheet for my son's teacher and I am runnning into a problem obtaining a students average. In one row I've listed the possible questions for all the tests whether the test has been taken or not. In the row below I've listed "questions answered correctly" for only the tests they've taken. I need a way to calculate the overall avergae of the student while ingoring columns with null values in the "questions answered correctly" row.
I know there is a way to ignore zeroes by excluding them with an IF statement but I didn't want to do that in case one of the students actually did get a zero. As the attached example shows the simple sum function renders a 25% average while the average should actually be 50%.
View 2 Replies
View Related
Dec 11, 2008
I have a set up of investments. Say A1 through to G1. Then on the next row the investments range from A1 to G1 again. This continues down many rows. There are spaces such as D1 and D2 through E2.
A
B
C
D
E
F
G
1
ausbil........................
Starting in column H, I would like to list the investments but ignore investments that have already appeared on the row (ie list only the first occurence) and ignore the blanks as well. For example, D1, E1, F1 are to be ignored because of the blank, and the fact that ausbil and amp already appear.
In row 2, the blanks in D2 and E2 are ignored as well as the repeated 'amp' in F2. So the table should look like below from column H:
H
I
J
K
1
ausbil
amp
vanguard
bt
2
amp
becton
fidelity
bt
Is there a simple formula for this? It has to be in rows as I have at least 60 columns of data before these columns begin. There are at least 300 rows.
View 9 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Apr 18, 2013
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
View 1 Replies
View Related
Jun 28, 2013
I have a sheet with a couple thousand rows of data. I need to calculate the subtotal sum for the first 50 rows of auto-filtered data. I can't seem to figure out the formula that I would need to have to do this automatically. If for example my filtered data the 50th visible row is row 1300, currently i can write a formula "SUBTOTAL(9,Q1:Q1300) but if the filtering changes I would have to go and change the formula to replace Q1300 with QX, where X is the new 50th visible row number. I know there must be at least one way to do it automatically but it seems to be beyond my current skill level.
View 3 Replies
View Related
Nov 11, 2006
I have a large file and I am doing a subtotal here. I want to do further processing of the "subtotal" data (basically, I want to sort the list on the subtotalled sum values) and I would like to know if there is any method to delete all rows except the "subtotal" rows from the sheet other than the method described here, in another post in 2003.
Copy subtotals only - visible cells too complicated [SOLVED]
This works fine but I have the word Total in some of the columns I am sub-totalling.
Also, some of the entries in that column are very long strings to start with and when the subtotal is made by Excel, the details are truncated (probably to 255 characdters) and the word Total is not there.
View 7 Replies
View Related
Feb 10, 2007
i have an excel sheet with columns including city & order value. i want to group rows with the same city name, and then subtotal the order values for each city. how do i do this?
View 10 Replies
View Related
Jun 4, 2007
I would like to insert rows below each of the subtotals in column D and F in the attached sheet. The current code I am using can only add rows according to one column. I don't know how to modify it so that it works on both columns. Also, when rows are added according to column D, the new inserted rows are filled with colors and I don't know how to remove. Lastly, how can I run this marco in multiple sheets?
Sub InsertRows()
Dim i As Integer
Dim rRw As Range
Set rRw = Range("D1")
For i = 1 To WorksheetFunction.CountIf(Columns(4), "* Total")
Set rRw = Columns(4). Find(What:="* Total", After:=rRw.Offset(2, 0), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If rRw.Value <> "Grand Total" Then
rRw.Offset(1, 0).EntireRow.Insert
End If
Next i
Set rRw = Nothing
End Sub
View 5 Replies
View Related
Jun 22, 2012
a huge spreadsheet that is taking up way too much time. It starts out with 6,000-7,000 rows, but then, she does Subtotals & it grows to nearly 10,000 rows! Then she has to deal with each & every one of the Subtotal rows, by hand. Here is a small sample of the spreadsheet, after the Subtotals have been applied:
Manifest Dttm
Manifest No
Carrier Scac
[Code].....
This sheet has columns A-Q. The key columns for our purposes are K (Weight In Lbs), L (Pickup Charge) and M (Consolidation Charge). We need to add information to column R on each SUBTOTAL row only! On each row there will be an amount in EITHER column L or column M, but not both.
1) If the amount is in L and K is LESS than 488 then R needs to show 44.39.
2) If the amount is in M and K is LESS than 124 then R needs to show 3.82.
3) If either
a. The amount is in L and K is 488 or HIGHER or -
b. The amount is in M and K is 124 or HIGHER
THEN R needs to show a formula to do this: (L+M)/(K/100)
Is there a way to either put a formula in just the Subtotal rows to accomplish this or (preferably) have a macro enter either the 44.39, 3.82 or the formula? I was able to come up with a formula that gets the correct amount in col R. Here's my formula:
Code:
=IF(AND(M3=0,K3123))),SUM(L3:M3)/(K3/100))))))
View 6 Replies
View Related
Aug 6, 2009
Any VBA code to delete rows in a subtotalled list?
The subtotal line and the associated rows should be deleted when subtotal equals zero.
View 9 Replies
View Related
Jan 9, 2007
I have a large spreadsheet with 25 columns.
I need a macro to delete prior rows if SUBTOTAL in column K equal zero.
View 12 Replies
View Related
Oct 21, 2011
Import Financial System -Recaps Trial BalanceFrom Date:31-Jul-2011,To Date:27-Aug-2011,
Requested By:Jenny Drumm,30-Aug-2011 9:28 AMAge Range: 0 - 30, No. of recaps: 231
Broker File NumberAgeVendor/Co/DeptFirstBankAssistsFRTBRODTYLoadsInvInvClaimsGL AcctTRUETRUEBalanceCostFeeRecptsAdjRecptsROE VarLC Var
[Code] ........
There are more age groups, & some groups have hundreds of lines. Each of the total line in the report has a number plugged in by the system, which, because of rounding, may be off by several cents. I figured out how to move the rows with Broker File Numer, etc & the row after it up above the first Age Range.
What I need to do next is:Delete all rows between "Age Range: ....." and the start of that range's data.Delete all blank rows between "Totals" and the next "Age Range: ...."Delete 5 rows after "Grand Total"Put a formula in the row immediately above each age range's "Totals" for each column, summing all the data for that column, in that age range. (Hopefully with the word Total in column B of that row, too)Put a formula in the row immediately above the "Totals" after "Grand Total" that adds together each of the Age Range totals.(I did figure out how to get the "Balance" cell in each row to sum that row going right to left, so at least I got a start! For that I'm using
Code:
LR = Range("C" & Rows.count).End(xlUp).Row
With Range("R10:R" & LR)
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.FormulaR1C1 = "=IF(ISNUMBER(RC3), Sum(RC5:RC17), " & Chr(34) & Chr(34) & ")"
End With
View 2 Replies
View Related
Dec 1, 2008
writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below.
Develop a macro
1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.
2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):
The number of customers is dynamic i.e.it keeps changing every month
4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.
5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H
6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I
7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
View 9 Replies
View Related
Aug 1, 2012
I have a sheet in an excel workbook which I export to a separate file and then save as a text document, I need to remove the tabs in this file, however the file (example attached) needs to be in a certain format to be imported into a piece of equipment which has a proprietary file format. Part of this format is the 2nd row and 5th row must remain present and empty.
[URL]
View 4 Replies
View Related
Feb 5, 2007
way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.
What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?
View 12 Replies
View Related
Oct 23, 2008
I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....
View 10 Replies
View Related
May 29, 2014
I am trying to use the SUMIF function to create a formula that will read the visible cells in Column B and if any visible cells in Column B are equal to or greater than zero I want Excel to sum the corresponding values in the visible cells of Column A. Not so hard but I'm having a difficult time getting Excel to ignore data in hidden rows.
See attached file for and further explanation : Sample.xlsx‎
I've tried using SUMIF as well as the AGGREGATE function but no success, yet.
View 5 Replies
View Related
