Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Sumif Function Not Working

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


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?


View Answers     

Similar Excel Tutorials

How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
How to Find and Fix Errors in Complex Formulas in Excel
Here, I'll show you a quick, simple, and effective way to fix formulas and functions in Excel. It can be hard to f ...
Quickly See All Cells linked to a Formula or Function in Excel
Ill show you how to see the cells used in a formula/function in Excel and also how to tell which cells are using t ...
Loop Through an Array in Excel VBA Macros
I'll show you how to loop through an array in VBA and macros in Excel.  This is a fairly simple concept but it can ...

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
and =SUMIF(J27,"2",J18) will. What do the $ signs do.
is there a way to copy and past and get this
becase no matter what I try I get this
=SUMIF($J$27,"2",$J$18) this across a row thanks Steve

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:


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?


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?

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!


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,


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.


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


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:


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


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?



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


  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 have used excel for 20 years. I am not an expert, but when the copy/paste function is not working properly, I am frustrated. Here's what is happening. In cell A I put a formula combining other cells. When I try to copy cell A to cell B, the correct revised formula appears in the box, but what is copied is the value in cell A. I have not instructed it to copy to value, have just used the plain old copy/paste by right-clicking the mouse.

Am I crazy? Anyone ever experience this?

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.


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


=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

Thanks in advance,


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,

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

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

Michael M

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 is =SUMIF('1'!E5:E10,"Free",'1'!D5:D10)
Cell should be like this =SUMIF('2'!E5:E10,"Free",'2'!D5:D10)

Thanks In Advance,


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

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

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.

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,

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?


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

I will welcome any tips!

Thanks in advance,

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.

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


ive been using this Vlookupallsheets function with the associated macro, which i found on

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.

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.

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?


Thanks in advance!

I'm trying to add the absolute number formula =ABS(C2) to my spreadsheet. When I type the formula into, lets say D2, to reference C2 who's number is -99 it will return 99. But my problems is when I try to copy and paste special the formula down to other cells. When I paste the formula to other cells they all return 99 even if they have a different number. If I copy the formula down to D3,D4,D5 and so on, they will all continue to reference C2 instead of C3,C4,C5 respectively.

I've tried doing the same thing on a new spreadsheet and I can get it to work but not on my spreadsheet which has some standing information on it that will be there every time the user opens the sheet and is used to run the macro. The reason I reference this is because I thought maybe the problem is due to a formatting issue but everything seems normal.

Any ideas??????