Excel weird problem with SUMIF formula

[reko]

Hi everyone,

I have a weird problem with SUMIF formula.
My formula in cell R1046 is :

=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.
Thank you in advanced.

regards.

 

[Peter_SSs]

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]

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 :

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]

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 :

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

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]

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]

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]

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]

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!