What is the Difference Between <30 and .<30

[Hardeep kanwar]

Actually I am using Sumif function

In A2:A5 in sheet 1 is Date

Like
<30
<60

30
60

In Sheet2 in A2:A300

30

<60
<30
<60 and so on

Sheet2 B2:B300

100
200
300
500
600
800 and so on

Now When I use =sumif(Sheet2!A:A,Sheet1!A2,Sheet2!B:B)

And it shows 0 in all Cells

But one of My friend Suggest me to Put dot or Comma Before Data in A:A in
Sheet 1 and Sheet2

Like .<30 or ,<60 and so on

It works perfectly.

But unfortunately my Friend don’t know the why it happened

Could you please suggest me why it happened?

Thanks in Advance

Hardeep kanwar

 

[Fred Smith]

You'll have to show us the entire Sumif functions for us to decipher the
difference between the two.

Regards,
Fred

 

[T. Valko]

Adding the dots makes the criteria a TEXT string.

Referring to a cell that holds something like <30 as the criteria is the
same as:

=SUMIF(rng1,"<30",rng2)

Which means sum rng2 if rng1 is less than 30.

<30 in the criteria range is a TEXT entry so the result will be 0.

 

[T. Valko]

I guess I should've showed how to overcome that situation!

Try it like this:

=SUMIF(Sheet2!A:A,"*"&Sheet1!A2,Sheet2!B:B)

That forces the criteria to be evaluated as the TEXT string "<30" rather
than the logical expression "less than 30".

 

[Hardeep kanwar]

Thanks Sir

Great Clarification and Perfect Function.

Works like a treat

Hardeep kanwar

 

[T. Valko]

You're welcome. Thanks for the feedback!