Free Microsoft Excel 2013 Quick Reference

SUM formulas in excel

I'd like to use a SUM formula to add together the numbers in a column, however i would like to do this missing some rows out........ ie; A1:A11 plus A13:A20 plus A22:A30.

I would also like to do this with another column containg fractions. Any help would be much appreciated, thankyou!


Post your answer or comment

comments powered by Disqus
I have the code written to find the ActiveCell which is the first blank cell after the last column of data. However now within that cell I want to enter a formula which calculates a 'Total' which should sum up the ActiveCell -1 (same row but previous column) through cell i1 and then fill the formula down. This is a sample of how the xls looks like. The cells are in parenthesis :
Parent Name (H1) q1(i1) q2 (j1) q3 (k1) qtd (l1)

The Active Cell pointer is @ cell L2 and needs to have a sum formula which should calculate the sum from i1 through activecell -1 column i.e. k1. This is my current formula but its not working. It just pastes the sum formula in excel but does not do any calculation and I am not sure how to fill it down.

ActiveCell.Formula = "=SUM(ActiveCell.Offset(0, -1):H2)"
ActiveCell.Select
Selection.FillDown

Thanks for your help on this.

Hi
I have 3 file. one is consolidate date and other 2 is source.
now i want to link insert sum formula in console in gren colored cells only from soruce 1 & 2 green colored cell through VBA
i.e
in Console excel sheet cell BR.Formula = =+'[Source 2.xls]P&L-GL wise'!$B$6+'[Source 1.xls]P&L-GL wise'!$B$6 and the remaing grean colored cells also

Hi,
I was wondering how to be able to see the formula in excel and not just the result in a cell after running a macro that sum some values for example. I used in my code:
.Cells(4,2) = Application.WorksheetFunction.Sum(.Cells(4,3) + .Cells(4,4))
But still I only see the result and not the formula when I click on the cell.
Any help would be great.

Thanks

How do I create a formula in Excel that will countif or sum if before or
after a specific date? Whenever I put the actual date in the formula it
works, but when I reference to the cell the date is in it returns 0.

Stef,

The normal procedure is to insert a blank first sheet (to the extreme left), name it "FIRST", and
insert a blank last sheet (to the extreme right), named "LAST. Then change your formula to

=SUM(FIRST:LAST!A1)

and make sure that any new sheets are inserted between FIRST and LAST.

Beyond that, you could use a User-Defined-Function in VBA.

HTH,
Bernie
MS Excel MVP

"Stef" <Stef@discussions.microsoft.com> wrote in message
news:A8D04C46-45A9-47B0-9EDA-D9BFB3B70B2C@microsoft.com...
> Sure. I have the following formula; =SUM(Sheet2:Sheet4!A1) which calcs cell
> A1 on sheets Sheet2, Sheet3 and Sheet4.
>
> I will be adding sheets to this workbook and cannot guarantee that Sheet2
> will always be the first sheet and that Sheet 4 will always be the last
> sheet.
>
> I was hoping to update a cell with the info on which sheet is first and
> which sheet is last and have the formula adjust accordingly.
>
> Any thoughts?
>
> "Bernie Deitrick" wrote:
>
>> Stef,
>>
>> Normally, you would use INDIRECT (based on your scant description of what you want to do) but
>> INDIRECT doesn't work with 3D ranges. Perhaps you could offer a better description, and we can
>> come
>> up with a solution.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Stef" <Stef@discussions.microsoft.com> wrote in message
>> news:F381313D-8A73-4347-955B-F945EBAA6541@microsoft.com...
>> > Does anyone have experience with 3D formulas in Excel. Specifically I would
>> > like to chance a cell value and have the formula automatically pick up the
>> > new end point to the 3D formula.
>>
>>
>>

I am having issues with a sum formula in excel it gives an error because there are #N/A from the values I am trying to sum due to them being vlookup values.

Also if anyone can help I am trying the following:

Have put together a 'summary spreadsheet' which includes peoples names and days of the week and values against names of these people pulled from vlookups for each day of the week mon - sun (using the following formula e.g =VLOOKUP(A3,SATURDAY!$A$4:$F$31,6,FALSE)

I am now trying to calculate the average of the quantites to which I have pulled back for each individual. Issue is that some don't have a value (because there was nothing done on that day) so the averge is artificially lower because it is calculating an average over 7 days and not 5. Is there anything I can put in the vlookup formula above to:

1. Change anything as #N/A to 0 or 'not in' (an If statement possibly?)
2. For the average formula to only calculate based on the count the number of values over 0 or 'not in' (i.e average over week was 100 and work was only done Mon, Tue, Wed, Thursday the average would come out as 25 (100/4) not (100/7)?

Hope this makes sense?

Thanks

HOW TO: Use Functions in Formulas in Excel 2000

Formulas can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text. Functions are predefined formulas that perform ...

I am haveing trouble returning a value for a formula in excel , it may be a simple solution but I can not seem to find an answer on other excel help sites.

I have a calculation in one cell and I need to use the value returned in that cell to determine what needs to be populated in the next cell.

In cell AK2 I have the following formula: =IFERROR((AH2-AI2-AJ2)&"","")

I am trying to put the following formula in cell AL2: =IF(AK2="","",IF(AK2=3,"3",IF(AK2=2,"2",IF(AK2=1,"1",IF(AK2=0,"1")))))

I am assuming the issue has to do with the formula in cell AK2 because, if I clear out the formula in cell and just replace it with a number, the formula works.

Can you write a formula in excel that can reference the source/or dependent cells of a specific cell?

In other words: I label my tabs 1,2,3,etc. If I link cell A1 in tab 1 to cell A1 in tab two I would like to be able to write a formula in cell B1 of tab 1 that tells me that cell A1 is linked to tab 1. For a bonus I would like to be able to write a formula in C1 that tells me any cells that are using cell A1. For example if tab 3, A1 was linked to tab 1, A1.

To put it in to formula terms:

Cell A1 of tab "1" would look like this: ='2'!A2
Then I want to write a formula in Tab "1" Cell B1 that results in: "Tab 2" or even "Tab 2 Cell A2"

Can anyone help with this?

I have a formula that will put the name of a tab in a specific cell. This is great for tables of contents and creating titles to documents or footnotes, but it is a pain staking process to use this formula for the above purpose.

That formula looks like this:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,34)

I am currently using 2007.

Thanks to whoever is smart enought to figure this out!

Hi ia get form teacher to execute some formulas in excel but i don't now how to do it can someone explain to me how is the procedure to execute this kind of formulas in excel thanks in advance
This is the example

I have been trying to figure out how to write a formula in excel that does certain things. Here are the things I am trying to do.

1. if I put a number (1,2, 3 ect) in we will say c1 I want D1 to say WW1 or WW2 ect depending on the conflict.

2. If I put a number (1, 2, 3 ect) in say F1 I want a proper name to show up in G1. Like 1 in F1 and in G1 I want a name like Chicago.

I hope that someone gets the drift of my questions. I have not written any formulas for numerous years and I am totally lost in Excel, thus I am solicitating someones assistance.

thanks for your help.

Cheers
Larry

How do i set up a formula in excel that is the tenth root of 7 versus the
square root of 7?

I enter complex mathmatical formulas in excel spreadsheets frequently. If
something isn't quite right xl makes suggestions on how to correct the
formula. I never choose yes and then another warning pops up that tells what
it thinks is wrong. This is a waste of time and very irritating. Is there
any way just to turn all that assistance stuff off and have it place "error"
or something in the cell? I am perfectly capable of finding the problem
without the program's help.

Excel 2002, Windows 2000 Pro

I would like to have a formula in excel that checkes "date modified" of the
file and shows this in a cell, so a user opening a file can see when the file
was last saved/modified. I saw this when using SAP Business Warehouse tool
but it was probably part of the program. Is there a possibility to to
something like this with a normal excel file?

DEAR PROGRAMMERS
PLS HELP ME IN THE FOLLOWING:

USING LIST BOX, I MARK THREE SETS OF GROUPS FOR THEIR REWARD SUMS. SO
I USE THE MULTISELECT AND THEN USE VB TO TRANSFER THEIR NAMES. SO FAR
SO GOOD.

CHARLIE 100
ROMEO 200
TOM 100
1STGROUP (how to put sum formula in VB for this group)

ALFA 500
BETA 500
GAMA 500
TOP GROUP (how to put sum formula in VB for this group)

LIKE THIS I WILL HAVE MANY GROUPS. ABLE TO GET THE GROUPS ISOLATED BY
USING LISTBOX AND VB.
I AM UNABLE TO WRITE THE SUM FORMULA IN VBTO AUTOMATICALLY CALCULATE
THE TOTAL SUM OF THE BONUS AWARDED FOR THE FIRST GROUP AND TOP GROUP
AND SO ON.

PLS HELP

WHAT I AM UNABLE TO DO IS AT THE END OF EACH GROUP I AM UNABLE TO
ENTER THE SUM FUNCTION AS THE NOTATION IS DIFFICULAT AND NO IDEA HOW
TO MAKE THE STATEMENT IN VB USING OFFSET OR HOW TO MAKE OUT IN VB HOW
MANY CELLS TO THE TOP IT MUST ADD. LIKE SUM( D20.D10).

THANKING YOU
CAPT VENKAT RAJARAM

i want to type Algebra formula in excel sheet, e.g 1) Aa root of 2) Aa * Bb

when you writing 4th, automitically th wil go in to small word, like that
even the root sq. formula needs to come

can some one give tips on this

thanks in advacne

saravanan

Help, I need to write this as a formula in Excel
Below £30.multiply by 5.25% , Above £30,the first £30 multiply by
5.25%,then the remainder multiply by 3.25% ,Hope this makes sense,
Thanks Woodie

Is it possible to lock a formula in Excel so that it cannot be changed, but
still allow the cell content to be changed?

I am trying to get a monthly payment figure by using the payment formula in
excel. It always calculates the payment incorrectly. I try it on my
financial calculator, as well as a financial program I have in the computer
and these two come up with the same figure. Then I try it in excel, the
answer is different.

the formula I am using is: =PMT(.0015,360,375,000)

Any help??

I am trying to paste various formulas in excel clipboard, but what it's
copying to the clipboard is only the content of the cells. How do I get to
paste formulas only?

I could use some assistance in finding out if there is a way to write a
formula in Excel to track how long data has been entered into a database

$25.00...
Hide Formulas In Excel Without Sheet Protection...I can see the code on the webpage but I don 't know what to do with it...Could someone put it into a worksheet for me?

Thanks,
Craig

How to use basic & advanced array formulas in Excel

Basic Arrays

Advanced Arrays

Training: Learn how to figure out dates using formulas in Excel 2007

How many days are between now and your next vacation? This course will show you how to use Excel to find out.


No luck finding an answer? You could always try Google.