# Excel weird problem with SUMIF formula

#### reko

##### New Member
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.

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

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

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 :

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.

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

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 :

What do you think of this kind of problem ?

Thank you once again, Peter.

#### reko

##### New Member
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

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

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

Replies
1
Views
367
Replies
1
Views
466
Replies
3
Views
443
Replies
5
Views
278
Replies
1
Views
355

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.

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.

### Which adblocker are you using?

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

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