SumIF not working

[lucky3]

hi all

i tried sumif formula but it wont work with a dateif function e.g.

if i type the numbers in
107 108
107 108
107 108

using sumif(A1:A3,107,B1:B3) would return 324

however the formula i have for the 107s are
for example
=DATEDIF(E19,Sheet2!$A$2,"d")/365.25

which equals to 107, but when i try to sumif like the above formula i get 0 not 324

can someone help me?
the format for both are numbers

[dilipandey]

Hi lucky3,

Welcome to the forum.

Would it be possible for you to upload a sample file. Thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

Hi lucky3,

You are saying

using sumif(A1:A3,107,B1:B3) would return 324

As a check, can you check if a1:a3 has 107 only and not with decimal parts i.e., 107.xyz or 106.xyz etc...?

Regards,
DILIPandey

<click on below 'star' if this helps>

[lucky3]

thanks
I did a quick check and yes it does have decimal parts which is why it didnt work. So im now wondering is there a way to use the first part not the decimal part for the sumif?

[dilipandey]

Ok . .

Try lookin at TRUNC function which can be used in your case. Post the file if u face any issue. Thanks.


Regards,
DILIPandey

<click on below star if this helps>

[lucky3]

Yep i got it to work :D

instead of the TRUNC i used the ROUND function.

Thank you very much

[dilipandey]

great....!!

Round does the rounding of number and Trunc removed the decimal part... both can work in your issue.. but TRUNC would be more recommended in this case. Thanks.

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[lucky3]

Hi 1 more quick question

when using sumif the criteria part doesnt change when i fill down the range. theres no $ symbols so im wondering if there is something that i can do?

the criteria i have is a number e.g. i want it to change from 2 to 3 to 4 ...
Thanks

Edit: nvm i got it to work.. instead of the numbers i changed it to a cell number

[dilipandey]

Hi lucky3,

Yes... changing it to cell number using ROW function is what which can help here...

Regards,
DILIPandey

<click on below 'star' if this helps>