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".
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).
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?
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?
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’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.....
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.
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.
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.
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?
The Formula in C1 is =IF(B1=TRUE,SUBTOTAL(9,C2:C3),A1)
This formula is then copied down to C2 and C3. I want this formula to subtotal the next two numbers if the cell to the left is true. Right now it is giving me 0, but it should be giving me 8.
I'd like to point a cell to a subtotal on another sheet but that sheet will periodically have the subtotals removed, more data added, then sorted and subtotalled again. Is there a way to have my other cell find the subtotal that matches a certain criterion (in this case, cost center)?
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:
I want to know how many emails are in my list. There is dash sign where the emal id is not available. The subtotal formula gives me the total entries in range but I want to know only email count numbers.
In my range there are 6 entries and only 4 email ids. Now I'm getting output 6 but I want it 4 ignoring 2 dash signs.
I'm looking for a formula that acts the same way as the subtotal formula (revises if rows are hidden and only adds visible rows). Is there anyway to get this removal of hidden rows but for subtraction?
I have a data set of students results and I have been using countif to pickup similar grades eg, C1, C2 and C3 all as C. However I need to subtotal filter against teachers and the sumproduct won't let me use C* as a wildcard to pick up the three types of C. How I can subtoal countif multiple criteria.
I'm looking for a SUBTOTAl IF function (if there's any) that would sum all the distinct values in a column for a given criteria based on another column. My data is listed below:
I'm looking for a formula to sum the DISTINCT Revenue values for Q1 2008, Q2 2008, and Q3 2008 based on the autofiltered list. The formula result should change dynamically based on the autofilter selection.
I have a list of numbers, where I use an autofilter to see for example the last 10 numbers, or the say the last 15 numbers. I need a formula that will give me the lowest number and the highest number according to the how I set the filter. The problem at the moment is the max and min formulae work when the autofilter is showing all the data, as soon as I autofilter the data set to show me the last ten numbers, the max and min formulae still calculate the whole set of numbers.If you set the filter to the top ten numbers the data set should be 11,12,13,14,15,16,17,18,19,20 with max being 20 and min be 11 not 1.
I've been trying to make a formula using autofilter to search data inbetween a date range which i would like to be specified from 2 ceels on the workbook.
Basically i'm trying to get it to function like access or a search box.
The bit i'm having trouble with is when i record the macro i get to the custom filter option but i am unsure how to link from there to the cells in the spreadsheet.
I have a auto filtered applied to 1000 rows of data and the actual quantity of rows I currently see is 55 rows. When I do a filldown command to apply this formula, "=CONCATENATE(X$1,I51)" the result I get is wrong becuase where the "I51" value is at the formula is picking up cell values that are hidden due to the applied autofilter.
What do I need to add to this formula so that it only picks up cell values that are not suppressed by the autofilter? I want it to disregard the hidden rows when I do a filldown and only apply the formula to the rows that are not hidden due to the applied autofilter.
I am trying to sum a column if Col B is equal to "TM" & Col A is equal to "Launch" then sum Col H. I am sure it can be done and I am hoping the right person reads this posts and enlightens me.
I come across a spreadsheet that is using DSUM formula, I did not understand why someone used DSUM instead of SUMIF formula, because using DSUM formula they had to use two extra cells for criteria, as seen in this example. I like to know is there any advantage of using DSUM instead of SUMIF in this example. The formule they used is =DSUM(J109:K113,2,$M$2:$M$3) and the criteria is G,D so they are adding all Debits in that column. I changed the formula to =SUMIF($J$110:$J$113,"D",$K$110:$K$113) that of course does not need $M$2:$M$3 range criteria.
G 3458224.37 D 3401317.50 C 3401317.50 C 56906.87 D 56906.87
i am using "sumif" formula in cell C5 and C10, the next step is to add up these two cells (sum up the figures of "sumif"), then the answer will be +15+6=21, how to do with using single formula.
The following formula finds the lowest five values in row 6 from column H to W. However if there are less than five values in the row the formula returns 0. I need to tweak it to return the smallest 5 values weather there is one or a full row of 16. Also if there are any of the same values I still want only the five lowest.
