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 dont know the why it happened

Could you please suggest me why it happened?

Thanks in Advance

Hardeep kanwar

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

Regards,
Fred

"Hardeep kanwar" wrote in message
...
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 dont know the why it happened

Could you please suggest me why it happened?

Thanks in Advance

Hardeep kanwar

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.

--
Biff
Microsoft Excel MVP


"Hardeep kanwar" wrote in message
...
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

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Hardeep kanwar" wrote in
message ...
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

Thanks Sir

Great Clarification and Perfect Function.

Works like a treat

Hardeep kanwar



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Hardeep kanwar" wrote in
message ...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Hardeep kanwar" wrote in message
...
Thanks Sir

Great Clarification and Perfect Function.

Works like a treat

Hardeep kanwar



"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Hardeep kanwar" wrote in
message ...
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