Using SumIf With Subtotal Formula And Autofilter

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


ADVERTISEMENT

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 View Related

Make SUMIF Formula Calculate With SUBTOTAL Function

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

Combine Sum / Sumif And Subtotal

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

Subtotal Formula - To Show Up In The Subtotal Value Column

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

Sumif And Autofilter Explanation

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

How Do I Create A SUMIF To Ignore Cells Hidden With Autofilter

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

Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

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

Sort By Subtotal, Make The Subtotal Stand Out

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

Adding Subtotal Formula With VBA

Apr 30, 2009

I am desperately trying to add a simple subtotal formula but receive the same error ("Type mismatch"):

View 4 Replies View Related

Formula To Subtotal The Next Two Numbers

Aug 21, 2007

The following is what I have in A1:C3.

4 TRUE 0
4 FALSE 4
4 FALSE 4

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.

View 9 Replies View Related

Can You Point A Formula To A Subtotal

Jun 12, 2009

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)?

View 9 Replies View Related

Combine Subtotal With SUMPRODUCT Formula

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

Ignore Dash Sign In Formula Subtotal

Jan 26, 2014

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.

View 10 Replies View Related

Subtotal Formula - Function 109 Does Subtraction (not Addition)

May 28, 2013

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?

View 3 Replies View Related

Subtotal And Countif In One Formula With Alphanumeric Values

May 9, 2014

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.

View 3 Replies View Related

SUBTOTAL IF Formula To Sum Distinct Values In A Column

Jan 2, 2010

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:

Column A Column B
(Quarter) (Revenue)

Q1 2008 $10
Q1 2008 $10
Q1 2008 $20
Q2 2008 $15
Q3 2008 $25
Q3 2008 $25

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.

View 9 Replies View Related

SUMIF Formula That Is Collects Values From An IF Formula

Aug 26, 2009

Can anyone tell me why this formula is not working?

=SUMIF($D$6:$AL$6,"=TM Goals",D12:AL12)

The entry under cell T12, which I am hoping this formula will pick up is based on the following formula.

=IF(E12="A",$L$5,"0")

View 12 Replies View Related

MAX And MIN Formula With An Autofilter

May 30, 2006

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.

View 2 Replies View Related

Autofilter Cell Reference Formula

May 21, 2008

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.

View 9 Replies View Related

Filldown Formula With Autofilter Applied

Mar 6, 2013

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.

View 1 Replies View Related

AutoFilter Method Of Range Class Failed - Yet Autofilter Works.

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

If Then SumIf In Same Formula

Jan 18, 2009

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.

View 9 Replies View Related

DSUM Formula Instead SUMIF Formula

Dec 19, 2006

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

View 9 Replies View Related

How To Sum Up The Cells Which With (sumif) Formula

Apr 21, 2014

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.

View 7 Replies View Related

SumIf Formula For Two Different Criteria

Dec 28, 2013

I need a formula which can sum the mode of payments separately occurring on different dates.

See the attachment. It is like

table 1
date cash credit card
1-1-14
2-1-14
3-1-14

table 2
product booking date booking amount payment date payment amount form of payment

a 1-1-14 100 2-1-14 100 credit card
b 1-1-14 150 3-1-14 150 cash
c 2-1-14 200 3-1-14 200 credit card

I want to add value in table 1 from table 2. In front of date CELL Both cash and credit card amounts should appear separately.

Book1.xlsx‎

View 11 Replies View Related

Sumif Formula - Greater Or Less Than

Feb 23, 2014

Below is my formula that works for a specific date. F$8 is the date.

How could I change this if I want data b/w two time periods.

>=F$6, and <=F$8?

[Code] ....

View 3 Replies View Related

Sumif Formula: Three Ranges Add Together

May 14, 2009

The section I have highlighted in orange has three ranges added together how would I show this in a simplified formula?

=sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$85:$GA$85)+sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$44:$G A$44)+sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$41:$GA$41)

View 2 Replies View Related

Tweaking A SUMIF Formula

May 30, 2009

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.

View 2 Replies View Related

Sumif Formula With 2 Criteria

Jul 24, 2009

is there away to do the sumif formula with 2 criteria? See attachment

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved