# 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"):

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

Jul 7, 2009

I have a 3 layered table: all of the data, the subtotals, and then the grand totals (the subtotals added up). I did the subtotal funcation for all of the subtotals and for the grandtotal. The grand totals grabs all of the subtotals accept for one row. All of the 4 totals miss the one subtotal row. I looked at the formula and it is correct and the same as the rest of the subtotals in the table. This row is in the middle of the table and I did check it is included in the reference in the forumla. I do not know how to fix this, or if it is an error?

## Adding Count To Each Subtotal Line?

Mar 8, 2014

I use the Subtotal function to sum several columns during the subtotal function. I dont think I can run a subtotal doing the sums and counts. Is there any way to add the number 0f rows in each subtotal? Count? I'm not sure how to go to each blank row (subtotal line) and count/add the number 0f rows in that subtotal?

## Adding Number To Subtotal In Same Cell?

Nov 2, 2012

I am trying to calculate the percentage of a number, have it subtotal, and then add to that subtotal another number all within the same cell like so:

20000 + 5% = 21000 + 4000 = 25000

## Pivot Table: Adding A Percentage Field As '% Of Subtotal'

Nov 21, 2007

In the attached Excel file, there is a pivot table.

In the Data part of the table, there are two columns. The second is the exact same field than the first one but expressed as percentage (Field settings/Options/Show field as "% of" + "Total".

The problem is: I don't want to express this field as a % of Total ('Grand total') but rather as a % of Subtotal (e.g. Danemark Total).

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

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

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

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

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

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.

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

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

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

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

## Adding Today Formula In A Countif Formula

Dec 19, 2012

I have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.

=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.

My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)

This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")

## Use "ISERROR" In A Subtotal Formula?

Sep 17, 2009

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

## Adding A Row In Formula Using Vba

Jan 26, 2010

My problem is that I want to select a cell with a "Sum" formula ex.

Apr 18, 2008

here is my formula
=SUMPRODUCT((MONTH(\$A\$10:\$A\$624)=1)*(\$C\$10:\$C\$624="DO"))

(\$C\$10:\$C\$624="DO")) AND: "SD" AND "V"

how would i add these other two factors?

## Formula For Adding Up The Total?

Jan 31, 2014

I need a formula to know the total specially for each of the numbers in column A. Like for e.g when you see 1 in column A I need to to know the total by adding all the numbers just for 1 in column B. So the total for 1 in this sheet would be 10+85+5=100. So automatically the answer should come as 100.

Oct 7, 2008

Average in Cell J8 : J73
Handicap in Cell I8 : I73

What Im trying to achieve is the Lowest Average Plus handicap  1 = total

Example
Cell
J8 = 105
J9 = 169
J10 = 126
J11 = 155

Cell
I8 = 45
I9 = 35
I10 = 54
I11 = 21

Result 105 + 45 =150  1 = 149

## Adding A Note To A Formula

May 14, 2009

My formula is : =SUM(C8:N8). How do I add a "note" as opposed to a comment to this formula?

Jul 18, 2014

I have a number of cells, with a rule as follows, but within each cell the company name and month vary, over 836 cells, so I cannot do a find and replace so wanted to know if there was a way of adding the following part to the rest of the formula within the 836 cells in one go, rather than tediously going through each cell and copying/pasting.

The part I would like to add to the various cells - 'Master Quote Sheet'!\$H\$18:\$H\$6021, "Won"

Existing formula (the varying parts within the different cells are the "02.2014" and "Company Name"
=SUMIFS('Master Quote Sheet'!\$J\$18:\$J\$6021, 'Master Quote Sheet'!\$E\$18:\$E\$6021, "Direct", 'Master Quote Sheet'!\$A\$18:\$A\$6021, ".02.2014", 'Master Quote Sheet'!\$D\$18:\$D\$6021, "Company Name")

## Adding And Copying Value, Not Formula

Nov 16, 2009

I need a code that looks through Column A (account numbers) then adds up the profit from Column X for each account number. The total profit for each account is then sent to different sheets. Account 100 profit sent to Sheet S1, Account 200 profit to Sheet S2, and so on.The profit figure will go to Column C (Sheets S1,S2,S3..), in the next empty cell.

I have a code here that works, but problem is it places a formula in the cells in the output sheets. I dont want any formula there, just the value that is calculated. This is because everyday there will be new values in the 'Summary' sheet, and if the macro inserts the same formula everyday I data(profit figure) from the previous day gets distorted.

## Adding A Formula With R1C1

Nov 29, 2009

I am trying to insert a formula into a VBA Code but I get an error msg.

The formula is;

## Adding Offset To A Formula?

Jul 4, 2013

Currently have a formula to enter the last value in a row in cell B1. What i need is the formula below in B1 then a formula in A1 which will show the value before the last entry in row 1. Maybe an offset unless there is an easier way

=INDEX(1:1,MAX(IF(D1:Z10,COLUMN(D1:Z))))

i.e.If H1 was the last entry in the row I want a formula for H1 in cell B1 and a formula for G1 in A1

## Adding Formula To VBA Code?

Jul 11, 2014

I have the following code which works plus a formula in cell range G5:G123....how do i add the the formula to the VBA code

Formula =IF(F5"".F5< today () +3 )

## Formula For Adding 1 Month

Jun 19, 2007

I'm making a basic spreadsheet that has to calculate monthly due dates for 'reviews' based on an initial start date.

im using:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

.... i know thats probably dead simple to you guys!

which works fine.. except when it comes to times when the start date is for example 31 january, so the sequence goes:

31 jan....... 03 March.... 31 march

and my problem is i need 1 review in each calendar month, so i need to tell that 03 March to be 28 Feb instead.

## Adding 1 More Item To This Formula

Sep 12, 2007

Here is my current formula:

=IF(ISERROR(INDEX(Sheet3!\$D\$5:\$D\$200,MATCH(E5,IF(Sheet3!\$C\$5:\$C\$200=C5,Sheet3!\$A\$5:\$A\$200),0))),"0",INDEX(Sheet3!\$D\$5:\$D\$200,MATCH(E5,IF(Sheet3!\$C\$5:\$C\$200=C5,Sheet3!\$A\$5:\$A\$200),0)))

I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.