# Subtotal Formula - To Show Up In The Subtotal Value Column

Oct 23, 2008I’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 RepliesI’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 RepliesADVERTISEMENT

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?

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.

The above formula works great and is something I am using in many of my spreadsheets. I have a question though, is it possible to use a simliar formula to show the value of the cell when filtered for example if you wanted to show a name?

--------------------------------------------------------------------------------

guessed that you filtered to display "yes" values....Evidently, no.

If the data with yes/no values is in B1:B100, with B1 as the col heading

This formula counts the occurrences of "yes" in the visible filtered range:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100) -ROW(B2),0,1))*(B2:B100="yes"))

Note: there are NO spaces in that formula

Is that something you can work with?

===========================================

I have a sheet with about 3000 lines that looks something like this:

A...............B..................C

1234........$42.................Subtotal of all values in group "1234"

1839........$58.................Subtotal of all values in group "1839"

1837........$15.................etc

1234........$11.................etc

1592........$19.................etc

The numbers in column A refer to a specific "group" number - anything in this group I want to subtotal into column C.

I'm not sure how to do this though. I've looked at the =SUBTOTAL function, but I'm not sure exactly what to do to make what I'm trying to do work.

I wrote a For Each loop to go through each cell and add values, but that took like 4 hours to run - so.. that's not the way to go lol.

I have a problem Im wanting to subtotal a range of filtered totals based on column A. Basically skip any filtered total where column A is blank

Column A Column B

735512172626

So when using subtotal 109 it will skip 5 as there is no figure in column A

I am currently using the below code to create the subtotal.

LR& = Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row

With Range("H" & LR& + 2 & "," & "I" & LR& + 2 & "").Offset(3, 0)

.FormulaR1C1 = "=SUBTOTAL(109, R[-" & LR& & "]C:R[-1]C)"

This question is probably simple for some of you but I can't seem to figure it out. All I am doing is grouping data by company name in column A and taking the value of orders in column P and subtotaling them. What I would like to do is offset the subtotal to be in column Q instead of totaling in column P. This is my code for the subtotal:

Sub CustomerWarrantySubtotals()

Dim EOF As Integer

Sheets("Customer Warranty").Activate

EOF = Columns("A:A"). find(what:="", lookat:=xlWhole).Row - 1

Range("A1:P" & EOF).Select

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(16), _

Replace:=False, PageBreaks:=False, summaryBelowData:=True

'Is there anything in here that will let me change what column to put the total in?

End Sub

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

View 4 Replies View RelatedThe 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.

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 RelatedNeed to insert 2 blank rows and sum columns (Columns B & D; range varies) based on cell value change (Column A). In attachedment I have before and after examples.

View 6 Replies View RelatedJust 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).

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

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?

View 3 Replies View RelatedI 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 RelatedI've reached a point in this macro where I'm stuck with NO clue how to achieve this, or even whether it can be done. In the Before sheet below, I need to bold the cells in H & I if the word "Total" is in G. I could probably manage that part, but here's the weird part: I need to add a formula to H & I of each row with "Total" in G to sum the numbers above it, from the previous total down to the current total. But there's no telling how long each range might be on a given report; from 1 row up to 8 rows.

THEN, in the second row below the end of columns H & I, I need to put a formula totalling all of the preceding Totals

I wonder if there might be a way to tell it to put a formula in H by each cell that has "Total" in G, with the formula summing or subtotalling everything from the cell in H that has data in F to the cell just above the "Total".

Attached File : Acct Activity.xlsxâ€Ž

I have a column A1 that consists of a range of values separated by a comma.

row 1:value 1, value 2, value 3, value 4

row2: value1, value 4

Row 3: value 2, value3

I want to get the unique counts for each value in the filtered list. This one field is not filtered but the others in the table are so I only want the counts of value 1-4 when the entire list is filtered.

I have been able to accomplish this with individual values using the following formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET('1. Respondents'!$C$7:$C$39312,ROW

('1. Respondents'!$C$7:$C$39312)-MIN(ROW('1. Respondents'!$C$7:$C$39312)),,1)),

--('1. Respondents'!$C$7:$C$39312=C4))

This works great other than the fact that I need to count total values of the column in an array.

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?

Trying to do a Subtotal in VBA...

View 13 Replies View RelatedI 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 RelatedI 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 RelatedI 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)

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 RelatedI have a table that calculates both grand totals and sub-totals, with one cell always showing the grand totals, and one cell that would show different sub-totals based on which filters are applied.

I am using the following formula to find the grand total:

{=SUM(IF(ISERROR(DL14:DL1541)," ",DL14:DL1541))}

I am attempting to use this (non-working) formula for the sub-total:

=SUBTOTAL(9,DL14:DL1541)

The problem is that I have a lot of cells with "#N/A" in them, and can't figure out how to get the sub-total formula to work. I've tried rewriting the sub-total formula in several different ways, but can't get it to work.

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

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 RelatedI 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.

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.

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
Copyrights 2005-15 www.BigResource.com, All rights reserved