Excel weird problem with SUMIF formula

reko

New Member
Joined
Feb 5, 2008
Messages
29
Hi everyone,

I have a weird problem with SUMIF formula.
My formula in cell R1046 is :
Code:
=IF(SUMIF(C:C,C1046,L:L)=0,"paid","not paid"

So, the basic is :
If column C contains the value of the regarding row then calculate the value of column L.

xl01.jpg


As we can see, inside the blue box - Excel calculate those 12 selected rows of column L correctly.
But the formula say that it's "not paid" as it should say "paid".

The next image below :
So I select L1046 cell, and then put the cursor in the formula bar, and then hit ENTER....

xl02.jpg



Now... the formula in R1046 and R1047 calculate correctly which show "paid" as it seen in the image below.


xl03.jpg


I know I may do the same for the rest of the row in order the formula calculate correctly,
but it's going to be tedious as there are thousands of rows that I should do the same thing repeatedly.


The next image show a weird result of the formula in R1048 and R1049 after I omit the IF formula....
so the formula in R column is as seen as in the formula bar of the image below :


xl04.jpg


And again, if I select L1048 cell, then put the cursor in the formula bar then hit ENTER,
the formula in R1048 and R1049 calculate correctly where the result is 0 (zero).

If I did something wrong when inputting the value in column L,
but then why Excel inside the blue box of the first image can calculate it correctly ?

Now my question is :
Why did Excel SUMIF formula not calculate correctly in my case ?
What did I do wrong before ?

BTW, I've tried (again and again) formatting the L column to "number" but no luck.

Any kind of respond will be greatly appreciated.
Thank you in advanced.

regards.
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,263
Office Version
  1. 365
Platform
  1. Windows
I would try two things.

1. Restrict the row ranges to something more like the size of your data as I suspect you have nothing like a million rows of data.

2. Round the result as Excel sometimes does not calculate things as exactly as if we did them ourselves.

So, say you might have 6000-8000 rows, try this

=IF(ROUND(SUMIF(C$1:C$10000,C1046,L$1:L$10000),2)=0,"paid","not paid")
 

reko

New Member
Joined
Feb 5, 2008
Messages
29
I would try two things.

1. Restrict the row ranges to something more like the size of your data as I suspect you have nothing like a million rows of data.

2. Round the result as Excel sometimes does not calculate things as exactly as if we did them ourselves.

So, say you might have 6000-8000 rows, try this

=IF(ROUND(SUMIF(C$1:C$10000,C1046,L$1:L$10000),2)=0,"paid","not paid")
Hi Peter, thanks for the fast respond.

Here is the result after following your advice :

xl05.gif


As we can see, I deleted all the rest of the rows except row 1 to row 13.
Also I narrow the range in the SUMIF formula.
The result still a problem :(.

The image below is where I put a formula in column S without IF formula as seen in the Formula Bar in the image below :

xl06.gif


What do you think of this kind of problem ?

Thank you once again, Peter.
 

reko

New Member
Joined
Feb 5, 2008
Messages
29
It seems that there is a formatting problem when via VBA I copy-paste the value into column L.

But if it's "true" that I made a mistake,
I still don't understand why Excel can calculate properly as seen in the blue box of the first image in my first post.

If I put a formula in R2 like this :
Code:
=sum(L2+L3)
the result show :
-2.32381E-10 ---> if I format the cell "general".
0 in black ---> if I format the cell "number" choosing "with minus sign for negative value" option
0 in red ---> if I format the cell "number" choosing "with red color for negative value" option
(0) in red ---> if I format the cell "number" choosing "with red two colon for negative value" option
(0) in black ---> if I format the cell "number" choosing "with two colon for negative value" option
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is the result after following your advice :
You didn't quite follow the advice. :)
If you look at your formula in R2 compared to my formula, you will notice some $ signs missing from yours. It doesn't matter that you have reduced the number of rows in the formula (for testing) but those $ signs are important. Be careful that you place them in the correct spots.
 

reko

New Member
Joined
Feb 5, 2008
Messages
29
I would try two things.

1. Restrict the row ranges to something more like the size of your data as I suspect you have nothing like a million rows of data.

2. Round the result as Excel sometimes does not calculate things as exactly as if we did them ourselves.

So, say you might have 6000-8000 rows, try this

=IF(ROUND(SUMIF(C$1:C$10000,C1046,L$1:L$10000),2)=0,"paid","not paid")

Hi Peter... stupid me... my mistake.
I forgot that I should edit each formula on each cell since the cell reference will also change after I drag to copy to another cell.
After I change the range back to C:C and L:L, it works !

Thank you very much and I am very sorry for my confusing reply before, Peter.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,263
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I forgot that I should edit each formula on each cell since the cell reference will also change after I drag to copy to another cell.
It won't require any editing cell-by-cell if you use the formula structure that I gave you. If you put the $ signs where I did and leave them out where I did, you can just copy the formula down & the parts that are supposed to change will change and the parts that are not supposed to change won't.


After I change the range back to C:C and L:L, it works !
You should definitely avoid using full column references like that as there is no need for Excel to calculate over a million rows if you are only using a few thousand.
 

reko

New Member
Joined
Feb 5, 2008
Messages
29
It won't require any editing cell-by-cell if you use the formula structure that I gave you. If you put the $ signs where I did and leave them out where I did, you can just copy the formula down & the parts that are supposed to change will change and the parts that are not supposed to change won't.
Wow.... this is new to me. I never know about this.
Thank you for your explanation, Peter.

You should definitely avoid using full column references like that as there is no need for Excel to calculate over a million rows if you are only using a few thousand.
Oke, I will follow your explanation above. I won't do the "full column references" like before anymore.

Thank you once again, Peter.
I really appreciate it.

regards.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,263
Office Version
  1. 365
Platform
  1. Windows
Wow.... this is new to me. I never know about this.
Thank you for your explanation, Peter.
You're welcome. I didn't want you to think that you might have to manually edit hundreds (or thousands) or formulas! :eek: :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,825
Messages
5,766,666
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top