Hi

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

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

- Excel not adding proplerly... - sample.xls (0/1)
- SUMIF not adding a range
- SUMIF not adding a range
- SUMIFS with criteria/sum ranges in a cell drop-down
- Excel does not display entered values correctly
- Excel does not display entered values correctly
- Why would SUM(range) function not add ".5"
- Adding summed cells in a conditional sumif
- Adding a range based on SumIf
- Excel could not save all the data and formatting you recently added
- SUMIF when using a range with critera
- Modify SUMIF to find data range in addition to value
- SumifS Multiple Sum Ranges
- Sumif to return a blank if sum range is blank
- SUMIF SUM Range
- SUMIF with Horizontal sum range
- Not adding up
- SUMIF - Excel 2007 SUMIF - How to Use Excel SUMIF
- Excel could not save all of the data and formatting you recently added to......
- Excel 2k doesn't read csv correctly from web source
- SumIf - when I fill down the Range, Criteria & sum range changes
- Excel could not save all the data and formatting you recently ad..
- Improve SUMIF function to sum more than one column
- Excel Do Not Auto Correct HSA into HAS, HSA is a common term

areas) that excel is not adding the distances correctly. I'm just

wondering why when you add Cells 11M and 24I, it is returning a result

of 421.0, and not the correct answer of 421.1

Thanks for all the help, in advance.

=SUMIF(B3:B7,"Check",C3:E7)

But it doesn't add all the values in the range C3:E7. It adds only some of

them.

I can make it work by listing each column range to be added separately, like

this:

=SUMIF(B3:B7,"Check",C3:C7)+SUMIF(B3:B7,"Check",D3 7)+SUMIF(B3:B7,"Check",E3:E7)

But it's a major pain to enter that whole thing each time.

Any idea why it won't add a range?

=SUMIF(B3:B7,"Check",C3:E7)

But it doesn't add all the values in the range C3:E7. It adds only some of

them.

I can make it work by listing each column range to be added separately, like

this:

=SUMIF(B3:B7,"Check",C3:C7)+SUMIF(B3:B7,"Check",D3:D7)+SUMIF(B3:B7,"Check",E3:E7)

But it's a major pain to enter that whole thing each time.

Any idea why it won't add a range?

Here's the outline:

I want to use two dropdown cells as references for the criteria in the SUMIFS function. For the criteria, this is simple since it is just numeric. But for the criteria range and sum range, I am trying to reference a cell containing a data-validated drop-down list of the named ranges in the sheet. So far, I have gotten to this functioning formula:

=SUMIFS(INDEX(Salad,,2),INDEX(Salad,,1),">="&B4)

where "Salad" is one named range. What I want to be able to do is refer to a cell that contains a drop-down list with all the other named ranges (Salad1, Salad2, Salad3) in it but Excel keeps giving me a #REF! error.

Thanks!

correct values in cells when they are typed. The program arbitrarily assigns

a decimal value to whatever number is typed. For example, if "1111111111" is

entered, it is displayed as "11111.11111" I have gone through cell

formatting, detect and repair and other options, but none have worked. The

program use to work fine. It started this recently and it won't go away. If

it helps, when a number is typed in another program, i.e. notepad, and copied

and pasted into Excel the value displays fine. The problem lies in actually

typing values into Excel. This is a problem that I am unfamiliar with. Any

help is greatly appreciated.

Thank you,

John

correct values in cells when they are typed. The program arbitrarily assigns

a decimal value to whatever number is typed. For example, if "1111111111" is

entered, it is displayed as "11111.11111" I have gone through cell

formatting, detect and repair and other options, but none have worked. The

program use to work fine. It started this recently and it won't go away. If

it helps, when a number is typed in another program, i.e. notepad, and copied

and pasted into Excel the value displays fine. The problem lies in actually

typing values into Excel. This is a problem that I am unfamiliar with. Any

help is greatly appreciated.

Thank you,

John

TSL1050@hotmail.com

If I use =SUM(C32+D32+E32+F32+G32+H32+I32) it adds it all up just fine.

All cell formatting is General. The first formula will add the decimal if it's greater than 1. So it will add up 1.5 but not .5

Some additional info that may help is that the ".5" is not actually entered. That value is calculated from a VLOOKUP formula. If I delete the formula and simply type in .5 the original formula works but Excel adds a preceding "0" in front of the decimal. "0.5" is what is actually displayed. I don't want this because there are a ton of numbers on the worksheet and I need it as simple and uncluttered as I can get it. If the second formula works with just .5 then at least I know the ".5" is recognized as a number and doesn't need the preceeding zero. Why does individual cell addition differ from range addition???

I'm sure this is a very elementary problem, but for the life of me I can't figure it out.

by the sum formula.

I am using the following conditional if statement:

=SUM(IF($A$27:$A$38=P27,$L$27:$L$38,0)

Cells in column L add up other cells in column K to come up with a value.

However, the sumif statement does not give me the correct answer. It gives me

the total of that column instead of adding the corresponding values in the

cell that match cell P27.

P L Value of cell L column A28 is

2003

2003 =sum(k1:k5) 50

2003 =sum(k8:k17) 80

2004 =sum(k18:k22) 20

Instead of giving me an answer of 130 I get 150.

I am trying to write a sumif which will sum values across a given range of rows and columns if the criteria is met - however I am running into issues and not getting the correct sum total. Can you please help me...thanks

In the attached example - I should get a sum of 25 for South region sales week 1- 3 but with the formula

=SUMIF(C7:C10,"South",D7:F10)

I end up with just 4 (seems that it is just summing up the values for south in the first column). I have heard SUMIF has some limitations but not sure if I am doing something wrong ??

One of our users sent me an Excel file of 6 MiB.

It has 7 worksheets. Most of them have 30 seconds to calculate the sheet, however there are no real

calculations, just a few concatenated string.

My first impression is that this is yet another example of Excel

(ab)used as a database.

The problem:

When the user tries to save (or save as), he gets the following

message:

Excel could not save all the data and formatting you recently added to

.

* To return to your workbook without saving click Cancel.

* If you want to continue saving changes without repairing the

problem, click OK.

[OK] [Cancel] [Help]

Help gives a blank help page.

It appears the last added (or copypasted) rows in the sheet have lost

their validation. I can add validation again by copy - pastespecial -

validation, but Excel won't save that change.

I have found a KB with the same error message:

http://support.microsoft.com/kb/215783

This talks about more than 2050 rows of conditional formatting. It is

possible that there are more than 2050 rows with conditional

formatting, but this does not explain why the data validation is not

saved.

I found another KB:

http://support.microsoft.com/default...b;EN-US;213904

It talks about more than 4000 different combinations of formatting.

I'm not sure, but I don't think there are more than 4000 different

combinations. Anyway, data validation is not the same as formatting,

so I don't see this as relevant either.

I have removed all conditional formatting from the sheet, and I have

set most of the formatting (font, border, background,...) to the

default values. I still get the error.

What is going on and how can I solve this?

--

Amedee

--

John

MOS Master Instructor Office 2000, 2002 & 2003

Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

"Kristopher" wrote:

> Cell A1 has the text "apples" and cell A2 has "oranges". Cells B1:B20 have

> the text names of many different fruits, including apples and oranges. Cells

> C1:C20 have the prices I paid for each of the different fruits in B1:B20. I

> want to add all of the amounts I paid for the apples AND oranges.

> SUMIF(B1:B20, A1:A2, C1:C20) doesn't work because SUMIF won't accept a range

> for the criteria - I can get a sum for one criteria (apples OR oranges), but

> not both.

>

> My real world application of this problem uses 11 different criteria instead

> of just two. The criteria may change depending on what text is in the cells,

> so I'd really like to just point to the cells these criteria are in and ask

> Excel to see if my range matches any of those criteria, not just one of them.

>

> Thanks!

meet the sumif and date range critiera?

=SUMIF('1563'!$F$9:$G$55202,"groceries",'1563'!$D$ 9:$D$55202)

-btw, it is near impossible to search the discussion group right now to find

recent postings. And the nofication of replies is not working as well in the

discussion groups.

Sumifs + Sumifs, but would rather not.

Thank you.

--

Thanks!

Dee

I want a sumif formula that sums the target range if any of the cells

matched are not blank, if all of the cells matched are blank then it

should return a blank (and not a 0). If the sum range does have any

values (including 0!) then it should return the sum.

Example

Col to Match Col to Sum

A B

1

2 23

1

1

3 0

3

So in column C I need my sumif formula. It should return BLANK for all

rows with 1 in column A, 23 for row 2 and 0 for all rows with a 3 in

column A.

Any ideas?

I have a SUMIF function that calculates a range that I need a total of as shown below:

However, I don't want it to calculate a range I$6:I$9. Is there another function that can be used to not have a sum range and choose specifc areas in the spreadsheet to do the sum?

For example I'de like it to do this:Any help would be appreciated.

I'm trying to use the sumif function in excel 2010 to find a row in a worksheet then only sum certain columns in the row. Below is sample date and the formula I'm currently using.

Example data:

Column: A B C D....

Row 1: Account#, period 1, period 2, period 3....

Row 2: 105320410, 10, 20, 30...

=SUMIF(A1:D2,105230410,C1:D2)

The result is only the first cell in the range, or 20, instead of the desired result of 50.

Thanks in advance for your help.

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

The SUMIF function, one of Excel's MATH functions, is used to sum up the number of cells in a selected range that meet certain criteria.

I have been working on a spreadsheet for a few weeks that is forecasting supply of products, this is something we have not done before so as much info is put together on each product as possible.

We have a total of 80 products on the main sheet, that then pull all sales history, etc from a database sheet in the same workbook.

I have finished revising some of the formulas on the first product so am now copying this down and changing the product code (all calcs reference this cell) to give me all products.

This gave me a 19Mb sheet!

I have now reduced it right down with just minimal data on each product and have all sales info frozen at the top and will hopefully be putting in a dropdown menu so that the spreadshet only has to calculate once (i can only look at one product at a time - these are the only really complicated sumproduct formulas.

The product detail now only has a few basic multiplication calcs and one sumproduct (x 52 as for each week of the year)

With 1 product the sheet is down to 1mb, 10 products is now 1.4mb, however if i go to 14 i am getting the following message, as i was with the 19mb sheet

Excel could not save all of the data and formatting you recently added to....[sheet name]

Is there a limit on the calculations on a sheet and can this be removed?

Any help is greatly appreciated as it has taken me weeks to get this far and then hit this wall!

Cheers

Neil

When receiving a csv from a web source, IE6 automatically hooks you to excel. Problem is, excel doesn't receive it correctly. It all comes into the first cell of each row, ignoring the commas. Excel seems to treat the data stream as a set of strings, not csv.

Then when telling excel to save the received data as a csv, the result from excel is a quoted string for each row, with quotes of course added to any already embedded quoted data. Now I have quoted strings with double quoted embedded strings. Ok for text file but was supposed to be a csv. Importing again to excel as either text or csv, there is no way to get rid of the enclosing quotes easily and it's still interpreted incorrectly by excel. ( and access and anything else MS)

So, I went to file explorer and changed the association of csv from excel to notepad, then went to the web and downloaded the csv again. Same result, no difference. I thought that would disconnect excel from IE6. I really wanted the file to be saved directly without excel getting in the way. There is no option on the web site to save the data as a file (javascript button not a data link, that's why)

Finally, I went into the registry and went to HKLM/Software/Classes/.csv and deleted the primary key Data Value. (It was: Excel.CSV and I simply erased it.) Of course I left the key structure in place.

Went to the web again to get the csv data, and this time a file save dialog came up for the csv file, which was what I really wanted anyhow. This must have been done by XP due to missing excel hook to csv. Finally was able to disconnect excel csv from IE6. I saved the data, opened in notepad and inspected. It is a perfect csv file.

I opened excel and imported the saved csv file using the defaults from excel and it loaded 100 percent correctly!!!

Q: Why didn't excel read it correctly from the web?

Q: Why didn't changing the file association to notepad remove or change the csv class key value?

Q: Is there a fix you're aware of for excel 2K to read a csv structured web data stream correctly? Is there some obscure excel or IE6 setting or something I need to try?

Of course I could easily write a script to fix the excel-hosed version of the streamed-in csv, but that's not the point. Excel should understand the incoming data the first time.

Tx for your advice.

Robert Phillips

criterial advance by 1 - field in the next file. Is this possible?

Also, will excel keep the info (function) straight as I sort the file?

Thanks much

links to an external Excel worksheet; conditional formatting and a lot of

formulas. I have taken a copy of everything on the most recent workbook and

pasted it into a new, fresh worksheet because the old worksheet started

displaying this message:

"Excel could not save all the data and formatting you recently added to *.xls.

To return to your workbook without saving, click Cancel.

If you want to continue saving changes without repairing the problem, click

OK."

Now, the new worksheet is showing the same message.

This workbook contains about seven years of data (the original was 30MB

before I set up the new one)

The new one (with only one worksheet) is only 752k - so, it is not a memory

problem, as I thought it might be.

How do I locate and repair the problem?

Thanks

column, but it would only sum the first column.

Desired Function

=SUMIF(F16:F21,"A",G16:H21)

Workaround

=SUMIF(F16:F21,"A",G16:G21)+=SUMIF(F16:F21,"A",H16 :H21)

Mark

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

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

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

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc