how to use sumif function to check date in 1 sheet is < 2 sheet

[Bharat Saboo]

I am trying to add a total in 2 sheet if date in 1 sheet is <= particular
criteria.

the following is my function call

=SUMIF(List!$B$2:$B$65536,"<= A2",List!$C$2:$C$65536)

Please explain why it does not work and how to make it work.

The answer at present comes to zero only.

Thanks

[SteveG]

Try using this array formula

=SUM(IF(List!B1:B23<=A1,List!C1:C23,0))

Instead of hitting Enter after typing in the formula, press Ctrl-Shift-Enter simultaneously. That will result in the formula having curly brackets around it like.

{=SUM(IF(List!B1:B23<=A1,List!C1:C23,0))}

The reason your other formula was not working was that the criteria "<=A1" was not true. You can't use a cell reference in quotes. If you had entered in the actual data that appeared in A1 sya a date. "<=12/1/2005", it would have worked. I assume that the data in A1 will change.


HTH

Cheers,

Steve

[B. R.Ramachandran]

Hi,

The formula should be

=SUMIF(List!$B$2:$B$65536,"<="& A2,List!$C$2:$C$65536)

Regards,
B. R. Ramachandran


"Bharat Saboo" wrote:

> I am trying to add a total in 2 sheet if date in 1 sheet is <= particular
> criteria.
>
> the following is my function call
>
> =SUMIF(List!$B$2:$B$65536,"<= A2",List!$C$2:$C$65536)
>
> Please explain why it does not work and how to make it work.
>
> The answer at present comes to zero only.
>
> Thanks

[Bharat Saboo]

Thanks

It worked.

"B. R.Ramachandran" wrote:

> Hi,
>
> The formula should be
>
> =SUMIF(List!$B$2:$B$65536,"<="& A2,List!$C$2:$C$65536)
>
> Regards,
> B. R. Ramachandran
>
>
> "Bharat Saboo" wrote:
>
> > I am trying to add a total in 2 sheet if date in 1 sheet is <= particular
> > criteria.
> >
> > the following is my function call
> >
> > =SUMIF(List!$B$2:$B$65536,"<= A2",List!$C$2:$C$65536)
> >
> > Please explain why it does not work and how to make it work.
> >
> > The answer at present comes to zero only.
> >
> > Thanks