Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Sumif Function Not Working

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I have a file using sumif function. when i copy and paste the formula to other cells, it was noted that the result remains the same as the original cell. I checked, those absolute reference ($) are correct in its places. However, when i manually keyed the formula into a cell, it works.

Do you know any reason why it is not working when copy and paste method is used?

Thanks

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

Similar Topics







I would like to know the difference between =SUMIF($J$27,"2",$J$18) and =SUMIF(J27,"2",J18)? I also need to know why this
=SUMIF($J$27,"2",$J$18) will not copy and past to a row and automatically change column letters example
=SUMIF($J$27,"2",$J$18)
=SUMIF($K$27,"2",$K$18)
=SUMIF($L$27,"2",$L$18)
and =SUMIF(J27,"2",J18) will. What do the $ signs do.
is there a way to copy and past and get this
=SUMIF($J$27,"2",$J$18)
=SUMIF($K$27,"2",$K$18)
=SUMIF($L$27,"2",$L$18)
becase no matter what I try I get this
=SUMIF($J$27,"2",$J$18)
=SUMIF($J$27,"2",$J$18)
=SUMIF($J$27,"2",$J$18) this across a row thanks Steve


In cell S132 I have a formula. Is there a way to perform the cut and paste function of the formula into S133, drag the formula down to S163, but also keep the original formula in S133? If I try the standard copy and paste function the reference cells in the formula change and are incorrect. The cut and paste function keeps the correct reference cells in the formula but I lose the formula in S133. I need the formula to stay in S133 because I plan on having two formulas that I would like to be able to switch back and forth between easily in a macro.

Any help would be great!


I am using Excel 2007. I have a report that I had exported from our CRM tool. I was copy and pasting the data from that exported report into my main Excel worksheet. This was wokring fine for about 10 minutes, all of a sudden the function stopped working. When I would go to paste into the main sheet, the cells would appear blank. I tried manually entering a number in the exported sheet, and tried to copy and paste that to the main sheet and that did not work either.

But if I manually type a number into my main sheet, and copy and paste that within the main sheet, the paste function works fine.

Any reason why the copy paste function would stop working between two worksheets when it had been working fine?


Hi all i was wondering if this can be done.

I have a formula in column G like this:

=IF(A1="","",0 & A1 & ":00")

I have a sumif in another column:

=SUMIF($J:$J,"OT2",G:G)

The sumif doesnt work as its not seeing the results as the column its testing against is also a formula, if i do copy paste special paste as value, the sumif works and returns the right value.

So....can sumif return a result from another formula?

Thanks


I have a formula in a spreadsheet which starts with If(and(....,....) which references to date ranges to select cells, which then continues with sumif(...:...:...).
When I copy the formula into the cell above this, keeping all criteria exactly the same I get #VALUE! as the result. Even if I Cut, then Paste I get the same outcome, then even more strange, if I then Cut and Paste it back to the original cell below it now gives the correct answer.
The formula is obviously accurate as it gives the correct answer, until it is placed in the cell above.
Can anyone help with this strange situation?
I have manually typed in the same formula and it still gives the #VALUE! outcome!


Hi,

I do quite a bit of copy/paste from files in one XL application to files in another. Most of the time, the copy/paste function works fine. Then, for no apparent reason, it stops working. At this time, if I copy, it will appear in the Clipboard of the other XL application and if I highlight the cell I want the data in and then click on the data in the Clipboard, it will insert it. But it won't copy/paste it.

Sometimes it will start working from a reboot, but not always. Sometimes it will just start working again. It's a bit of a mystery.

Also, there are times when I copy/paste in the manner described above and I will lose all the formatting in the target file. It will look OK when I copy/paste but when I reopen the file, all the formatting is gone.

I'm not sure if they are related issues or not but I'd thought I'd mention them both. Any help would be appreciated.

Hello,

So I have run into a very strange and extremely annoying problem: I am working on a large and complex set of Excel files in Microsoft Excel '97. Each file contains about 15 worksheets, in which 13 of the 15 worksheets have about 60 columns with data. None of the files reference cells in OTHER files.

Almost all of the cells contain a function of some kind, although not complex functions. Straightforward arithmetic (dealing with a complicated financial model). Now, here is the weird part: when I copy & paste a function into the next cell over, it gives me a #REF error. However, the function is correct and there is no reason for a #REF error to be delivered. When I paste THE SAME function into the SAME cell, the original #REF error vanishes, but a PRECEDING cell (usually in the same row but with no formulaic relationship to the cell I just changed) changes to a #REF function.

If I copy & paste the same function three times into the same cell, all #REF errors vanish. But then if I save the document, a #REF error appears again for no reason.

Has anyone else seen this problem? If so, is there any way to fix it? I have been told by colleagues that similar problems have been observed in Excel '03, but have not yet tried opening the files under a newer version of Excel...

All the best,
Chris


Hi,

i'm doing some analysis on a spreadsheet and am using the following formula:

=SUMIF(G2:G2189,X2,D2:D2189)

I want to copy that formula down my column. But when i do the usual drag, it changes more of the formula than i want it to. I want the formulas in the cells afterwards to look like this:

=SUMIF(G2:G2189,X3,D2:D2189)
=SUMIF(G2:G2189,X4,D2:D2189)
=SUMIF(G2:G2189,X5,D2:D2189)

So only the X cell reference changes. But whats happening is this:

=SUMIF(G3:G2190,X3,D3:D2190)
=SUMIF(G4:G2191,X4,D4:D2191)
=SUMIF(G5:G2192,X5,D5:D2192)

It would be ok if i was only doing it for a few as i could change it manually, but i have over 1000 cells i need to copy this formula into.

Can anyone shed any light on whether i can do this or not?

Thanks

Andy


Hi I hope this explains what I am Trying do
I have this automated to the K column
you can see my data grow by 1 column per day
with 3 random cells with data for column L through R
I use =sumif($a$12:$J$12,"sun.",a1:iv1) which means
I have edit this formula everyday for each of the seven days
then copy down column for each day very time consuming
thank you for any help


Sheet2

  A B C D E F G H I J K L M N O P Q R S T U 1   1 1               #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   2 1       1           #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   3 1 1         2 1     #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   4     1 1 1     1 1   #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   5       1   1     1   #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   6     1               #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   7       1           1 #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   8 1 1               1 #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   9         1 2         #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   10             1 1 1 1 #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!   11 3 3 3 3 3 3 3 3 3 3 #REF! #REF! #REF! #REF! #REF! #REF! #REF! #REF!       12 Fri. Sat. Sun. Mon. Tue. Wed. Thu. Fri. Sat. Sun. All Sunday Monday Tuesday Wednesday Thursday Friday Saturday last # of days   13 8/1/08 8/2/08 8/3/08 8/4/08 8/5/08 8/6/08 8/7/08 8/8/08 8/9/08 8/10/08                       14                                           15                                          
Spreadsheet Formulas Cell Formula K1 =SUM(#REF!) L1 =SUMIF($12:$12,"sun.",#REF!) M1 =SUMIF($12:$12,"mon.",#REF!) N1 =SUMIF($12:$12,"tue.",#REF!) O1 =SUMIF($12:$12,"wed.",#REF!) P1 =SUMIF($12:$12,"thu.",#REF!) Q1 =SUMIF($12:$12,"fri.",#REF!) R1 =SUMIF($12:$12,"sat.",#REF!) S1 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T1 =DATEDIF(S1,NOW(),"d") K2 =SUM(#REF!) L2 =SUMIF($12:$12,"sun.",#REF!) M2 =SUMIF($12:$12,"mon.",#REF!) N2 =SUMIF($12:$12,"tue.",#REF!) O2 =SUMIF($12:$12,"wed.",#REF!) P2 =SUMIF($12:$12,"thu.",#REF!) Q2 =SUMIF($12:$12,"fri.",#REF!) R2 =SUMIF($12:$12,"sat.",#REF!) S2 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T2 =DATEDIF(S2,NOW(),"d") K3 =SUM(#REF!) L3 =SUMIF($12:$12,"sun.",#REF!) M3 =SUMIF($12:$12,"mon.",#REF!) N3 =SUMIF($12:$12,"tue.",#REF!) O3 =SUMIF($12:$12,"wed.",#REF!) P3 =SUMIF($12:$12,"thu.",#REF!) Q3 =SUMIF($12:$12,"fri.",#REF!) R3 =SUMIF($12:$12,"sat.",#REF!) S3 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T3 =DATEDIF(S3,NOW(),"d") K4 =SUM(#REF!) L4 =SUMIF($12:$12,"sun.",#REF!) M4 =SUMIF($12:$12,"mon.",#REF!) N4 =SUMIF($12:$12,"tue.",#REF!) O4 =SUMIF($12:$12,"wed.",#REF!) P4 =SUMIF($12:$12,"thu.",#REF!) Q4 =SUMIF($12:$12,"fri.",#REF!) R4 =SUMIF($12:$12,"sat.",#REF!) S4 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T4 =DATEDIF(S4,NOW(),"d") K5 =SUM(#REF!) L5 =SUMIF($12:$12,"sun.",#REF!) M5 =SUMIF($12:$12,"mon.",#REF!) N5 =SUMIF($12:$12,"tue.",#REF!) O5 =SUMIF($12:$12,"wed.",#REF!) P5 =SUMIF($12:$12,"thu.",#REF!) Q5 =SUMIF($12:$12,"fri.",#REF!) R5 =SUMIF($12:$12,"sat.",#REF!) S5 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T5 =DATEDIF(S5,NOW(),"d") K6 =SUM(#REF!) L6 =SUMIF($12:$12,"sun.",#REF!) M6 =SUMIF($12:$12,"mon.",#REF!) N6 =SUMIF($12:$12,"tue.",#REF!) O6 =SUMIF($12:$12,"wed.",#REF!) P6 =SUMIF($12:$12,"thu.",#REF!) Q6 =SUMIF($12:$12,"fri.",#REF!) R6 =SUMIF($12:$12,"sat.",#REF!) S6 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T6 =DATEDIF(S6,NOW(),"d") K7 =SUM(#REF!) L7 =SUMIF($12:$12,"sun.",#REF!) M7 =SUMIF($12:$12,"mon.",#REF!) N7 =SUMIF($12:$12,"tue.",#REF!) O7 =SUMIF($12:$12,"wed.",#REF!) P7 =SUMIF($12:$12,"thu.",#REF!) Q7 =SUMIF($12:$12,"fri.",#REF!) R7 =SUMIF($12:$12,"sat.",#REF!) S7 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T7 =DATEDIF(S7,NOW(),"d") K8 =SUM(#REF!) L8 =SUMIF($12:$12,"sun.",#REF!) M8 =SUMIF($12:$12,"mon.",#REF!) N8 =SUMIF($12:$12,"tue.",#REF!) O8 =SUMIF($12:$12,"wed.",#REF!) P8 =SUMIF($12:$12,"thu.",#REF!) Q8 =SUMIF($12:$12,"fri.",#REF!) R8 =SUMIF($12:$12,"sat.",#REF!) S8 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T8 =DATEDIF(S8,NOW(),"d") K9 =SUM(#REF!) L9 =SUMIF($12:$12,"sun.",#REF!) M9 =SUMIF($12:$12,"mon.",#REF!) N9 =SUMIF($12:$12,"tue.",#REF!) O9 =SUMIF($12:$12,"wed.",#REF!) P9 =SUMIF($12:$12,"thu.",#REF!) Q9 =SUMIF($12:$12,"fri.",#REF!) R9 =SUMIF($12:$12,"sat.",#REF!) S9 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T9 =DATEDIF(S9,NOW(),"d") K10 =SUM(#REF!) L10 =SUMIF($12:$12,"sun.",#REF!) M10 =SUMIF($12:$12,"mon.",#REF!) N10 =SUMIF($12:$12,"tue.",#REF!) O10 =SUMIF($12:$12,"wed.",#REF!) P10 =SUMIF($12:$12,"thu.",#REF!) Q10 =SUMIF($12:$12,"fri.",#REF!) R10 =SUMIF($12:$12,"sat.",#REF!) S10 =LOOKUP(2,1/(#REF!<>""),$A$13:$HO$13) T10 =DATEDIF(S10,NOW(),"d") A11 =SUM(A1:A10) B11 =SUM(B1:B10) C11 =SUM(C1:C10) D11 =SUM(D1:D10) E11 =SUM(E1:E10) F11 =SUM(F1:F10) G11 =SUM(G1:G10) H11 =SUM(H1:H10) I11 =SUM(I1:I10) J11 =SUM(J1:J10) K11 =SUM(K1:K10) L11 =SUM(L1:L10) M11 =SUM(M1:M10) N11 =SUM(N1:N10) O11 =SUM(O1:O10) P11 =SUM(P1:P10) Q11 =SUM(Q1:Q10) R11 =SUM(R1:R10)

Excel tables to the web >> Excel Jeanie HTML 4


I'm having ALOT of problems getting SUMIF to recognize the TODAY() function. Basically I'm trying to create a worksheet that will add up the total balances of Lines of credit if their expiration date is after todays date. I have tried including the TODAY() function in the formula for the SUMIF and I have tried to have the SUMIF formula reference a cell that has =TODAY() in it. Neither is working.


Hi

I'm trying to use the wildcard to lookup any year in a column within the sumif function. I've used this millions of times but it's suddenly stopped working.

I have two checkboxes to select years 2009, 2010, and a formula cell to provide 2009, 2010 * (if both are selected) and "" if nothing is selected.

The sumif formula uses this cell to lookup against, but it's suddently stopped working. Is there an option in excel I might have ticked to prevent it from working? When either 2009 or 2010 are selected it works, so I know it's something to do with the wildcard. I've used this formula on other sheets and I've checked it and it still works fine.

My sumif formula is below. AG2 is the cell with either 2009, 2010 or *

=SUMIF('inv days 2010'!P:P,Summary!AG2,'inv days 2010'!J:J)


Hi All,

I'm trying to sum the values in columns E to J in the dataset sheet based on three parameters being equal to the variables set by the sheet in cell E$7, C2 (absolute ref) and F7 (absolute reference) but the formula isn't working at all. Any ideas

Code:

=SUMIF(Revenues, CONCATENATE(E$7,$C$2,F$7),Dataset!E:J)


Is there a better way of looking up with three variables other than a concatenated column of the three variables

http://rapidshare.com/files/39011625...aset.xlsx.html

Thanks in advance,

Will


Hi,
I've noticed that when doing a paste link of a single cell, the resulting formula has absolute references. However, when doing a paste link of a range of cells the resulting formulas have relative references. I would like to maintain the abosolute references or avoid the automatic formula update of the relative reference so that my original formula's reference remains intact.

I have seen some solutions involving a brute force method of changing the original formulas to use absolute references but I'd like to avoid this. Are there any other ideas out there?

Thanks much for your help,
Gary


This has been bugging me for some time and I'm just wondering why it
happens.

I have a simple formula in cell D16 that multiplies numbers in two
other cells: =C16*A16

If I manually change the value in C16 the result in D16 is correct.
If I copy/paste a value into C16, again, the result is corect.
If I CUT/Paste a value into C16 then the formula changes to
=#REF!*A16.

Is there maybe a setting I have enabled that causes this or is it
working as designed? If so, what is the logic behind the Cut/Paste
that causes the #REF???

Thanks gang! This is no big deal, I'm just curious.



Dear All,

I'm having a work book that consists of 31 pages for the days then finally
a summary page.

On the summary page cell B16 there is a sumif function which is linked to the first page.i just need to update the formulas to the balance columns and rows,rather than changing it manually.Dragging and copy& paste method will not work.

Eg: Cell B16.it is =SUMIF('1'!E5:E10,"Free",'1'!D5:D10)
Cell C16.it should be like this =SUMIF('2'!E5:E10,"Free",'2'!D5:D10)

Thanks In Advance,

Regards,
Ak


Hi All
The following formula works fine
But I'm sure there is a simpler method

=SUMIF(R9:R10,">0",R9:R10)+SUMIF(R13:R19,">0",R13:R19)+SUMIF(R22,">0",R22)+SUMIF(R25:R27,">0",R25:R27)+SUMIF(R30:R34,">0",R30:R34)+SUMIF(R37:R42,">0",R37:R42)+SUMIF(R45:R46,">0",R45:R46)
The reason there are so many ranges is that ther are Sub totals in between that may be either negative or positive, so if I use SUMIF over the whole range the answers are incorrect.

I thought I had use a SUMPRODUCT on this previously, but can't seem to make one work.

Any input in making this a bit "cleaner" would br appreciated

Regards
Michael M


Hi, im working with office 2003 pro in Windows XP.

2 Excel questions:

Is there a way to make the "paste" option in excel "sticky" (that is, so that you can do some other work in excel then come back and click "paste"? currently excel makes you go back and recopy then only can you paste. You cannot do anything between "copy" and "paste".

Is there a way to paste so that the cell is exactly copied? What i mean is, so that the formulas that are copied are pasted exactly as they were (without changing references)? (i get the right result when draggin and moving a cell, except that the original cell is emptied - but u get the idea). A work around is to add absolute refrences, and then take them away later - or to copy the formula in the formula bar and then paste it into another cell (one cell at a time ... )

Many thanks,
Fred


Is it possible to replace the third part, the "sum_range"part of the sumif function with indirect formula? I have to sum quantities from data sheet by product number by date. On the data sheet each of the columns represent a date. However, I am unable to use the sumif function as on the working spreadsheet only every 4th columns represent a date. Hence I am unable to copy the sumif function across the columns. As I have a huge amount of data (hundreds of dates) changing the formula reference manually is not practicably.

Could someone help me please?

Thanks.


Dear all,

I would like to ask you for a help with simple but not working SUMIF function.

I have a row of dates (row 2) and in the end I have a cell N2 with "today" function.
Below there is a row of values (row 5) and in the end I have a cell N5 where I need to have a sum of values which are on or before "today" date.

I have put there SUMIF function as you can see, but it is not working


http://www.clichy.estranky.cz/fotoal...ii/original/83

I will welcome any tips!

Thanks in advance,
Tereza


Greetings. I keep getting this error and I have recreated this spreadsheet numerous times because I cannot figure out why this error continues to happen. or what it means. I use the "=sumif" formula all the time but I am only getting this error when I am working in this one file. Does anyone know what it means and how to fix it? I type this much: =sumif(range,.....) and as soon as I put in the cell reference (in my case it's A8) I get this goofy error:

hmmmm, I can't copy and paste it? what up with that? and I cannot include an attachment either? okay, if anyone can help me post a picture of this error I would surely appreciate it.


hi,

ive been using this Vlookupallsheets function with the associated macro, which i found on http://www.ozgrid.com/VBA/VlookupAllSheets.htm

everything works - accept when im looking up something on the new sheet, the ID number,for example, has to be in exactly the same format as the way it is on all the other sheets.

is there any way to modifiy this Vlookupallsheets function, so that its looking up only the value, regardless of whether the cell is as a text, number, etc...

Im guessing i have to somehow combine the IF function, so it Vlooksup Allsheets, IF the 'Lookupvalue' contains the text "(whatever number, figure, text string)" ?

The other problem I've got is, for some reason when I copy and paste the formula on subsequent rows, it displays '0'. When i click on the 'fx' button, the formula displays the correct result, i close the box...and it displays the correct result, instead of 0. What's that about? Is it something to do with the script not automating that process for all the cells if you copy and paste the formula?

would appreciate some help on this please, thanks.


Hello all,

This is my first post because I can usually just search to find answers, but not for this one. My problem is:

I have a SumIf formula to add up quantities that occurred between two specific dates. The formula works when I enter the dates manually, but I cannot get it to work when trying to reference the cells with the date in them. Here is the formula:

=SUMIF('Actual HDD'!$A$2:$A$65536 ,"

When I copy a formula and paste it into another cell it is correctly showing the new cell, however, the answer to the formula is not correct. It is just showing the answer from the originally copied cell.

It happens whether I use the pull down method or copy and paste. It is making me re-enter each formula manually in order to get a correct response.

Any thoughts on why this would happen?


I've been trying to copy and drag formulas on my spreadsheet by the references are not working. Example. I have =$B1 in cell A1. If I copy and drag that to A2 it remains =$B1. And it should change to =$B2, correct? Same as if if make the column absolute. Help and thanks.


Hello!
I am trying to use absolute cell reference in a sumif formula and continue getting an error. Can an absolute cell reference be used in sumif? below is my equation...maybe i am just using it incorrectly?

=SUMIF(SUM($F$40:$F44),">0",SUM($F$40:$F44)/SUM($F$3:$F8))

Thanks in advance!