Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Sum formula is not adding up properly

I am summing up hours in Excel and the sum formula is not working Properly.
For 2 of my 5 cells are adding correctly, but the other 2 when added to the
formula throw the entire thing off.

They are all formatted the same in 13:33 format to measure the # of hours
spent on an activity.

What would the reason be that two of them are not working? (it is almost
like exel is substracting hours when these 2 cells are added)


Post your answer or comment

comments powered by Disqus
Hi,

Im trying to turn a manufacturer code into a supplier code using a vlookup formula. I has changed the majority of them but there are some codes that the formula is not picking up.

I have applied a filter to identify the problem items and have highlighted the column that I need the supplier code. The supplier code on sheet 3 is column F.

I know that the text codes at the top of 'sheet 1' have trailing spaces and that they need to be trimmed but i need to some how change the format of the numeric codes below that that are throwing out #N/As.

Please can some one explain what i need to do to the format of these codes.

Thanks for you help

Sam

Is there a simple way to make a SUM formula expand to include a new row if
the row is added as the first or last row of the SUM range?

For years we have been frustrated that rows added to the extremes of a range
included in a SUM formula are not automatically included in the range used
in the formula. This is also true of many other functions.

It seems logical to us, that if we select any row within a range, and insert
a new row, the new row should be included in any formula addressing the
original range. On the other hand, if we select a row contingent to, but
outside a range, and add a new row, it is logical that the new row not be
included in the formula addressing the original range. To us this seems a
logical approach and gives the user a predictable way to quickly add rows
(or columns) and know if any forumula referencing the range will, or will
not, be automatically included in the range.

Nevertheless, the user currently has a problem if he/she adds a new row
above the first row or beneath the last row of a range formula range. Such
new rows are not automatically included in the original range reference used
by a formula. This is especially problematic if the user originally defines
a range of one row since a later addition to the range will never be
automatically included in the SUM formula addressing the original range.

Please note that the same problem we have with rows also applies to columns.

As a work-around we have used the OFFSET formula to address a larger range
than the actual SUM range. This works very well, but generates huge
overhead in developing the original formulas or worksheet. We have also
tried adding a phantom row of minimal height (or hidden), between the last
row of a range and the sum formula in a 'Totals' row. This allows the user
to add new rows at the end of the SUM range. But this two generates
significant overhead and complicates the worksheet.

Does anybody know of a simpler approach, or of any configuration change,
that would force new rows to be included in the original range? Thank you
for any suggestions.

Richard

Hi can anyone help me? I am trying to do montly bills on excel and since changing decimal hours to actual time the sums are not adding up, ie:

Charges:

shows in hours [hh]:mm (D6), then a cell for price per hour (E6) then a sum for time * cost per hour (F6) which I have down for =(D6*E6*24)

Then in the totals box I have

(J12) a simple box for carrying over last months figure
(J13) Payments received which is a link to alternate cell with an =sum figure for three payments
(J14) Charges =(F11-F16) F11 is an =SUM box for the above charges and F16 is another =SUM for any deductions.

Anyway.

When

Previous Period (J12) = £181.24
Previous payments (J13) = £181.24
Charges (J14) = £232.13

then total box (J16) contains the sum =(J12+J14-J13)

However it = £253.12

I am not sure if I have given you too much information or too less but J16 should show £253.13, I now it is only a minor difference but a big one for me, is there anyway in changing any of the above formulae to show the correct amount, I am not sure if it is totting up all the figures to show a rounded/un-rounded number.

Thanks for any help in advance.

Adam

While using VLOOKUP, the result is not showing up...only the formula. But
while I was actually entering the formula in VLOOKUP the value was showing
up. But once I hit OK only the formula showed up. Please help!!!

I have been using a spreadsheet for months without any problems. I go on leave for 2 weeks and another employee updates the spreadsheet whilst I'm away. I return to find that none of the formulaes are now adding up correctly and the other employee is now out of the country fo a month. Please help.

Here are a few examples of what's happening.
1/ a total box is adding up the values in a column. When I change a value the total remains the same. If I then re-formulate the total it corrects itself, but when you then change another value it remains the same again.

2/ a cell (B1) should show the value of the prevoius cell (A1) * £26.17. So if I put 10 in cell A1, B1 should read £261.70. But when I change the value in A1, cell B1 does not alter even though the formula is correct.

3/ I want the same formula to apply to a number of lines, so I would normally drag the formula in the first line down to the final line and it would correctly copy the fomula but changing the row letters. Now I find that when I drag down it copies the formula correctly but the value stays the same as what's in row 1.

Any help would be appreciated. Kevin in the UK.

I am making a timesheet spreadsheet with increments of 15 minutes.

Column D = Describes Function (Lunch, Admin, Projects, Etc)
Column E = Notes
Column F = =IF(D4="OUT","0.00",IF(D4="lunch","0.00","0.25"))

For each actual Activity I do, a value of .25 pops in for 15 minutes. For
each non-work activity I do, I want 0 to pop in. My problem is the result of
this formula is not coming up as a number and I am unable to add or sum the
numbers together.

Please help!!

Thank you

I noticed my formulas aren't adding up correctly. The first one should be -100 because of two dependents but the total is 50 dollars less and others are messed up also.

I have attached a speadsheet that is not adding things up that way I need them to. There are two spreadsheet in the attachement of this order form. What I am having a problem with is the second spreadsheet. The columns should search the first spreadsheet for any order number in Column E and then add what is included in the row for that Item # to the right. I want it to search the entire first sheet and add up all the values for that particular Item # which is in the cells to the right. I am using the following formula. Any help is greatly appreciated.

=IF($C6<>"",VLOOKUP(B$6,OrderForm!$E$5:$X$473,COLUMN()-1,0),"")

"Formula Is Not Valid" Changing Label Text with FORMULA()

The information in Microsoft Excel Macro Functions Help indicates that you can use the FORMULA() function to enter or edit a text label in a chart.

I am trying to assign a macro to a button, however, after placing the button
on the worksheet, the "Assign Macro" window is not coming up. Is there
another way to assign the macro to the button?
--
Ronnie

This is really annoying, my formula is not picking up words starting with the letter "Y" it will pick "Y" up on its own but not if you start a word with that letter, see my attachment and change the word "Alpha" in column A to "Yoda" or something begining with "Y" and you will see what i mean.

Thanks

DJ

This formula works everywhere else in my workbook except two spots.
I cannot see why it is not working.
=SUM(IF($G$375:$G$509="N",IF($M$375:$M$509=$N$375,$H$375:$H$509)))
I want it to take the total of column H(375:509), but only the cells where column G(375:509) shows an N and for those cells where M(375:509) matches the N375 cell. (Do you follow? I am not so good at explaining these and I don't really want to attach my workbook because its got proprietary information. Column G is going to be N or not N; M Shows the month; Cell N shows a specific month; H is a dollar Value)
But its taking the total on the whole of column G and then all subsequent formulas ($N$379, $N$384, etc) show a zero
This one:
=SUM(IF($G$210:$G$370="N",IF($M$210:$M$370=$N210,H$210:H$370)))
located a few rows above gives me the values that I need.
So whats the difference and why isn't the first one working properly?
This has been frustrating me for several hours today.
Can anyone help?

I put in =sum(E1+G23) & the total in the cell where I put the formula comes
up(2 or 3) cents off? It comes up short when I put =sum(E1*G23). Not al the
time but off & on. I have to add stuff on paper to check that the total is
right. I should not have to take all this time to use Excel.

Hi

The sumif formula in Excel 2010 is not adding the sum range correctly (not adding all the values). Please view attachment.

Hello,
I have a simple sumif formula and I noticed it's not adding up properly. In some cases, it's only adding up the first instance it sees a criteria.

=SUMIF($F$16:$F$66,K16,$H$16:$I$66)

K16 for example has 1 in it. F16 to F66 has 1 a few times and , 2s, 3s e.t.c.
But the formula is only adding up the 1st 1. I have checked if have formated columns H to I as currency and so they are not in text.
any ideas?

Thank you.

Hi,
I have a worksheet in a workbook where user can enter accumulated labor hours worked under a particular activity code on a daily basis. There are columns for 31 days. I like the user to see and use only 30 days columns for months which have 30 days and so on. For February users should see only 28 days or 29 days depending on if it is leap year or not. The format for the data is as follows:

Col-A Col-B Col-C Col-D ........... Col-AF Col-AG Col-AH Col-AJ
ActCD Description Day1 Day2 ..............Day30 Day31 Adjust AdjustedMTD
102 Truck driver 8.0 16.0 ..............100 2.0 102.0
103 Union Bus 8.0 16.0 ..............160 8.0 168.0

In other words Column C thru Column AG is used to enter accumulated hours.
Column AH is used to enter adjustment of hours for that activity. The Column AJ is the sum of hours for the last day of the month and the adjustment.
I like the user to be able to able to enter only hours and adjustments without worrying about changing the sum formula in the Col-AJ.
Through a table in another sheet I can pick up the beginning and ending dates of the month when user initializes the sheet for a new month.
So I am looking for a way to hide and unhide last 2 or three days columns based on the month of a particular year and dynamically change the sum formula in column AJ. For example for July 05 the sum formula is "=SUM(AG3+AH3) while in Sept 05 it should change to AF3+AH3 in row 3.

Hi guys, some of you may remember my project from last week in which I needed to sum only the "45" portion of cells containing the text

RCDOW-45-wk31

Within a large range of cells.

Aladin was extremely helpful and developed the following formula:

SUM(IF(ISNUMBER(SUBSTITUTE(SUBSTITUTE((LargeRange),$I107&"-",""),"-wk"&BK103,"")+0),SUBSTITUTE(SUBSTITUTE((LargeRange),$I107&"-",""),"-wk"&BK103,"")+0,0))

A marco may have been a better way to go as these array functions have to loop through thousands of cells, I have this array formula appearing about 100 times, and the result is that this thing is SLOW.

Sadly I was requested not to use a macro.

Now I am receiving complaints that my spreadsheet is too slow.

Maybe you cant have it both ways, but what I am asking is:

How can I make the above formula faster?

I have already been able to reduce the size of the range that the formula loops through, and I have successfully replaced the SUM function with SUMPRODUCT, which I read was a bit faster.

Some of my users have ancient computers and alas this array formula is causing freeze up after freeze up.

Any ideas? I would like to somehow use a non-array formula if possible, as I understand that tha main problem is that the array fnction is looping through the entire range.

Thanks guys!!!

Situation:

Formula is set up in the cell correctly and the numbers are correct but when I click and drag the formula to different cells the new cells all read the same end number even though the formula is changing. If I click in the new cell, click on the formula (but do not change anything), hit enter - the number changes to the correct amount per the formula.

I've tried doing a sequence of 3-5 cells with the formula's but all it does is repeat the same 3-5 answers even though the formula's in the respective cells change (so the formula isn't registering with Excel and computing the correct answer).

I've played with my bosses computer and the SAME spreadsheet does everything correctly on his (formula's add correctly etc), but don't on mine.

I don't get it =/

Any help is GREATLY appreciated
Chrystal

In cell d6 I am summing together d1:d5 using =sum(d1:d5).
it does not calculate correctly unless I type in the cell and F9 it.
Further, when I drag the formula right despite the next cell saying
=sum(E1:E5) it is returning the value of column D.

Any idea why the sum is not refreshing and why the drag right doesn't work?

Thanks!

I used the following:
=SUMPRODUCT(--(Sheet1!B:B="WARRANT"),--(Sheet1!F:F="E"),--Sheet1!C:C)

.... if there is WARRANT in cell in Col B and Col F contains an "E", then add
all the sums in column B that match these specifications ... but it is not
adding up my amounts in col C ... am I using the wrong formula?

Hi
I have a spreadsheet with app 800 names sorted in alphabetical order.
To the right of the names I have a few col on numbers. At the bottom of each
col I have a calculated total for that col(i.e. @SUM (c2...c800)
From time to time I have to add new names.
I usually type in the names below the 800 line and then do a sort on the
names. At this stage the name is inserted in the proper place BUT MY COL
TOTAL are no longer accurate. The formula at the bottom now read(as an
example) @sum(c12...c800)
How can I stop this from happening.
Thanks
--
Norton Professional 2004 says this email is clean...believe it

Hi,

Using Excel 2003 on XP.

Our users have access to enter information into a main database. I extract a daily report to check for anomalies.

Is it possible to use conditional formatting (or anything else) to identify if data in a cell is not formatted as Proper case? I have been checking this issue manually but the report is growing on a daily basis and will soon be too big for this manual process.

I don't want to change the case of the data in the spreadsheet because what I have there doesn't matter. I need to identify where the case is not Proper and then go back into the main database to amend the original data.

I thought conditional formatting might be able to highlight any cell not using proper case but I have no idea how I set up a formula for this. Can anyone help, please?

Any other suggestions/ideas gratefully received.

I have been using a spreadsheet on excel for some months and now it has decided not to add up any rows or colomns, even though a sum formula is clearly in place. I can copy the spreadsheet to another computer and it works fine. Is there a setting on excel on this computer that is causing the problem.

I am sure someone, somewhere can use the function, I absolutely hate it.

Can anyone assist me in turning off the hyperlink style format that appears
when an @sum formula is added to a spreadsheet in excel???


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