Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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

Get the Number of Workdays Between Two Dates in Excel
How to calculate the total number of working days between two dates in Excel.  This allows you to exclude all weeke ...
Output the Worksheet Name in a Cell in Excel - UDF
UDF to return the name of the worksheet in Excel on which this function is located. There are no arguments for thi ...
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
This free Excel UDF (user defined function) returns the first word from a cell in Excel. This extracts the first w ...
Output the Name of the Current Excel Workbook Including Extension - UDF
Free Excel UDF to display the name of the current Excel workbook, including the workbook extension. This function ...

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

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

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?


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.


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

I've been testing some functionality in a copy of a spreadsheet. I have it working fine. Now I want to copy the values AND functions from one worksheet in the test workbook to the original workbook. This is a one time copy.
I thought it would be a simple:
Highlight everything. Copy. And then paste into the original spreadsheet. For some reason, it copies all the values correctly but does NOT bring over the functions- it brings the value of the function. IE; =if(x > y) 1,2). It brings over the 1 or the 2 NOT the function itself. I want the function brought into the spreadsheet.
Using Version 1997-2003. What am I doing wrong?
I can't get paste function when I do a paste special. It doesn't show up as one of the options.

Hi everyone,

I'm hoping someone here can shed some light on this for me. It has to be possible, and probably pretty easily, but I'm just not figuring it out.

I have a worksheet that has Vlookup results in one column. I want to copy/paste the result of the vlookup function to another cell. When I do this currently I just get a 0(zero) - I'm assuming it's the vlookup function that's being copied and not the result from the original column (ie. 477 units).

How do I convert the VLookup function result cell into a value that will allow me to copy/paste the result (ie. 477) to another cell? I've tried changing the format of the cell but that doesn't seem to help.


I have 3 columns, on sheet Print,
I have a sheet called LOG where I want to get my Info from.
My problem is I can get my T/T with the formula below, but this does not work on Letters.
I am trying to find a formula, and the Microsoft Help is not much Help.
In the Column type I want to get Letters or words from the correct row representing the date on the left on the same row.
For example.
=sumif(log!a:a,b5,log! d: d) but my problem is that "D" on sheet LOG column contains words.

The is the Print Sheet A B C Column
Date Type T/T
row 5 =LOG!A1392 =SUMIF(LOG!A:A,B5,LOG!F:F)
row 6 =LOG!A1393 =SUMIF(LOG!A:A,B6,LOG!F:F)
row 7 =LOG!A1394 =SUMIF(LOG!A:A,B7,LOG!F:F)
row 8 =LOG!A1395 =SUMIF(LOG!A:A,B8,LOG!F:F)
row 9 =LOG!A1396 =SUMIF(LOG!A:A,B9,LOG!F:F)
row 10=LOG!A1397 =SUMIF(LOG!A:A,B10,LOG!F:F)
Ps I spent all but 10 mins spacing this out and now look at it.
how can I just copy paste a worksheet??


I am trying to use the Concatenate function within the criteria section of the sumIF syntax.


If I remove the concatenate function and replace with the intended result the SumIF function works perfectly. I am satisfied that there are not problems with the concatenate calculation itself. As soon as I nest in the concatenate function it stops working.

Can anyone advise if it is possible to nest a concatenate function in the criteria part of the SumIF function.

I am aware that I can easily use Pivot tables to acheive the result I require, however due to functionality requirements a pivot table is not suitable.

Thanks - any advice is appreciated!

I have been working on VBA to generate a cash receipts journal entry. I used the Macro Recorder to get the Sub done, so now it comes down to the Function portion. In the attached file, column H contains the corresponding General Ledger account number, with the first four digits being the fund number. I need to generate a subtotal cash offset line entry for each fund (subtotal of transactions for each fund). At first I thought I could simply nest a LEFT() function in a SUMIF() function- SUMIF(LEFT(H2,4),"1101",I2:I14), but that would just return a result of 0. I don't think DSUM or SUMPRODUCT allow a nested function, so I created a column to the right (this file is to be imported into our General Ledger, and my game plan is to hide the data by formatting it as Special and then with the ";;;" to hide it) with the LEFT(H2,4) function, then I tried to use a conditional sum formula with a nested SMALL function, but that doesn't work either. The file that I am working with has a few idiosyncracies- the number of records is variable, as are the fund numbers, so it isn't a simple matter of writing a few SUMIF(H2:H14,1101,I2:I14) and so on for each fund number. For some reason, the SMALL and LARGE functions don't seem to be working on the LEFT(H2,4) resultant column. I tried formatting them as General and Numbers, with the same result. I even tried copying the column, and doing a Paste Special with Values Only, and still the same result. My guess is that I will have to come up with a custom function in my VBA, but I'm a VBA newbie, so if anybody could help me get the ball rolling on this, I would appreciate that. Thanks!

This one is really odd, never have I seen anything like it. The forumulas are all very basic too.

Spreadsheet was made a couple weeks ago and now I need to update some of the numbers.

Take for example this:


B120 is 1

When I copy the formula to 122 it copies the formula but wont change the figure.

So anything I copy down just is the same value of 1 even though the formula is different.

But now I go into the cell and I manually retype the formula, it updates the number.

THe formulas are all right and they are supposed to do something but they are not working.

If I manually retype the exact same formula already in a cell that was pasted there from a paste special>formulas only it updates, but if its there from just the paste function it doesnt care and just pastes the original value from the cell it was copied from.