Autofilter With Subtotal Sumif
Nov 7, 2005
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?
View 9 Replies
ADVERTISEMENT
Jul 16, 2013
Just to give you a quick overview (i cannot attach workbook due to data protection for our customer), I have a workbook with two tabs on it, an invoice summary tab and a rentals tab.
This is an extract of the table from my invoice summary tab:
Tab
Net (Zero Vat)
Net (Standard Vat)
Initial Rentals
Rentals
0
[Code] .....
The invoice data is on the rentals tab. Column "C" on the rentals tab details what type of rental it is, as per column A in the table above. the amount to sum on my rentals tab is in column "S".
I have a formula currently which looks like this:
=SUMIF(Rentals!$C:$C,'Invoice Summary'!$A2,Rentals!S:S)
this works fine.
Now the problem i have is i have an autofilter on the rentals tab which will be filtered by different accounts, I need the formula on the invoice summary tab to only show the visible amounts (as per the filter) and not the whole column. I was thinking of a subtotal formula but can't use a sumif and subtotal together (i dont think).
View 8 Replies
View Related
Nov 29, 2012
I'm trying to put these two formulas together:
=SUM(SUMIF(STATUS,{"Pending","New","Issued"},PREMIUM))
=SUBTOTAL(9,PREMIUM)
I tried
=SUBTOTAL(9,(SUM(SUMIF(STATUS,{"Pending","New","Issued"},PREMIUM))))
and it did not work.
I also tried
=SUMPRODUCT(SUBTOTAL(9,(OFFSET(PREMIUM,ROW(PREMIUM)-7,,1))*(STATUS={"Pending","New","Issued"}))
this also did not work. I do not fully understand the SUMPRODUCT function. I found this formula online and substituted the values. It worked for the other spreadsheet which did not include a SUM on the SUMIF formula
(i.e. SUMIF(STATUS,"=Pending",PREMIUM)
View 6 Replies
View Related
Feb 21, 2014
I'm trying to exclude cells that are filtered in a separate sheet.
The sheet I'm working with gathers information from this separate sheet using the following SUMIF formula:
=SUMIFS(Claims!$H:$H,Claims!$G:$G,$A9,Claims!$B:$B,C$6,Claims!$E:$E,$B9)
This formula extrapolates information perfectly for me if I do not filter columns in the separate sheet with all of the information.
I know that SUBTOTAL functions can be used in a way to exclude hidden cells. Can I combine the SUBTOTAL function into my SUMIF function above to produce results that exclude filtered cells?
View 5 Replies
View Related
Sep 27, 2006
I managed to find a couple of responses and solutions to a problem I'm having but I can't quite seem to make it work in my example (probably because I have no idea on how to decipher the formula...)
The problem I'm having is that I have an autofiltered log on one worksheet and on a seperate worksheet, a summary reflects the autofiltered criteria but I also need the summary to break down the information by month.
The formula I'm trying to manipulate is:
= SUMPRODUCT(SUBTOTAL(3, OFFSET(C3:C9,ROW(C3:C9)-MIN(ROW(C3:C9)),,1)),--(C3:C9="John"),H3:H9)
But I've never used sumproduct or offset before so I'm not sure how to manipulate it... or if this is even the right solution!
View 8 Replies
View Related
Feb 5, 2009
I have a formula to search a sheet for certain criteria and sum up the total, data is entered into this sheet by week number, where I then filter it for whichever week I need. I following formula works fine, but adds all the cells including hidden ones, how can i get it to ignore hidden cells??
=SUM(IF('FT Line 1-RX'!$C$2:$C$2100='FT costs 1-Rx'!B$1,IF('FT Line 1-RX'!$E$1:$GT$1='FT costs 1-Rx'!$A11,'FT Line 1-RX'!$E$2:$GT$2100)))
There is raw data in the 'FT Line 1-RX' sheet which is summed up in the 'FT costs 1-RX' sheet.
View 9 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
Sep 25, 2009
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies
View Related
Oct 25, 2007
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies
View Related
Sep 17, 2009
I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
View 6 Replies
View Related
Aug 20, 2014
I can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
View 4 Replies
View Related
Apr 21, 2009
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
View 5 Replies
View Related
Feb 27, 2012
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
View 2 Replies
View Related
Jan 16, 2007
I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
View 2 Replies
View Related
Apr 3, 2009
Trying to do a Subtotal in VBA...
View 13 Replies
View Related
May 10, 2007
I seem to be experiencing a problem using the SUBTOTAL formula. Is it not possible to use subtotal in a running command? ie [A2] = SUBTOTAL(102,A$1:A1) then drag for a couple cells vertically. "0" is displayed for ALL dragged cells.
View 9 Replies
View Related
Jun 2, 2007
I am trying to sum the subtotal on a range eg A1:A10 but I want to total up only the Absolute value of the visible cells.
View 9 Replies
View Related
Jan 13, 2007
I have a table of 3 coulmns :
(A) branches codes ( 1,2,3,4,5.......)
(B) names
(C) values
I want to put a formula in coulmn (D) cells to calculate the subtotal of coulmn (C) next to the last time each code appears in coulmn (A)
View 7 Replies
View Related
Dec 26, 2007
I am wondering if it is possible to do a subtotal and then name each subtotal with a range name to use in a formula on another sheet?
View 2 Replies
View Related
Oct 15, 2008
I am trying to bring back the the first entry on a filtered col
i.e. a list would be
Ryan
Paul
Steve
Steve
Ryan
Paul
If i filtered on Ryan. I want my reference cell to = Ryan
View 2 Replies
View Related
Apr 30, 2009
I am subtotalling a range of data and the very last subtotal row appears about 70 rows away from the other data and then has the GrandTotal underneath.
View 2 Replies
View Related
Jan 5, 2010
I tried "googling" this, but I can't seem to find an answer. Is there a way in VBA to refer to the "subtotal" row(s) in a sheet? I have a large sheet that has a varied number of rows. Each month the data changes and I have to go in to the report, subtotal by one column and then enter a specific formula into the subtotal row.
Is there a way to reference the subtotal row in VBA so I can write a macro that will do this all for me? There are typically a varied number of subtotal rows and the locations of them change depending on the amount of data we have each month.
View 12 Replies
View Related
Mar 18, 2007
I have a spreadsheet that has columns for "Invoice Number," "Order Number," "Invoice Date" (DD/MM/YYYY), & "Amount." It is formatted using a macro.
I need macro code that will group all invoices by month and subtotal each month, then put a grand total at the bottom. Also a blank row needs to seperate each month.
View 10 Replies
View Related
Sep 11, 2009
What i would like to be able to do is subtotal all the sheets (Approx 190 Sheets) with subtotals in Column I and (K to AA) with each change in Column D Period reference D3 till the bottom the data is entered. As an example i have attached a sample with one sheet 77001 showing what result i would like.
View 7 Replies
View Related
Dec 13, 2007
I have added subtotals to a worksheet, as I have many times before, but this time I don't see any controls off to the left. I am at a loss as to why this might be happening or how to get them to display. Have I toggled something off or on that might be causing this?
View 4 Replies
View Related
Jan 15, 2009
I a formula in one of my cells so that when the data is filtered it will add up all the cells that have a yes in them. However I'm getting an error. If I use the countif formula it still adds all the cells with yes that are not in the filter selection.
View 4 Replies
View Related
Jul 31, 2012
I have a sheet with 8 columns.
I am using VBA to get the data from a larger worksheet.
Is there a way to get 2 subtotals in the sheet with only the 8 columns.
I have grouped this data by date (column 1) and used SUM on Column 8 to get the subtotals of each group.
I would also like to get the COUNT of the items of each groups by using Column 3 (it has alphanumeric data in it) - at the same time. Is this possible?
If not, how would I be able to get the COUNT without wiping out the SUM subtotals? This has to be done with VBA of course.
View 2 Replies
View Related
Aug 3, 2012
Is there a way to get median for a subtotal?
median(SUBTOTAL(1,G7:G1747)) is not working.
View 9 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
