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

Free Microsoft Excel 2013 Quick Reference

Excel SUMIF not adding sum range correctly

Hi

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


Post your answer or comment

comments powered by Disqus
On the enclosed attachment, you will notice (in the peach colored
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.

I can't get SUMIF to add the values in a range. I tried:
=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?

I can't get SUMIF to add the values in a range. I tried:
=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?

I've been scouring every Excel forum I can find for what I believe is a relatively simple problem, but still can't find the solution...

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!

I am currently running Excel 2000 on WinXP Pro. Excel does not display the
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

I am currently running Excel 2000 on WinXP Pro. Excel does not display the
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

I'm trying to add a row of 7 cells. When I use the =SUM(C32:I32) formula I don't get the correct answer. It's not adding one of the cells which has a value of .5

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.

I would like the sumif statment to add the values in cells that are generated
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.

Hi,
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 ??

Hi,

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

try =SUMPRODUCT(--(B1:B20=A1),C1:C20)+SUMPRODUCT(--(B1:B20=A2),C1:C20)
--
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!

I am using Excel 2007. How can I modify sumif formula to sum only cells that
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.

Is it possible to sum ranges from more than one worksheet. I know I do a
Sumifs + Sumifs, but would rather not.

Thank you.

--
Thanks!

Dee

Seemingly simple but actually difficult?

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?

Hello,

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.

Greetings,

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.

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

SUMIF - Excel 2007 SUMIF - How to Use Excel SUMIF

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.

Hello

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

Using winxp home ed SP2 and Office 2000.

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

I would like to fill down & keep the same range & sum range - but let the
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

I have a very large Excel workbook with about twelve worksheets that contain
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

In using the SUMIF function in Excel, I wanted to sum values in more than one
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

HSA is a new tax account similar to an IRA, not need to auto correct to HAS.

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


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