Hi all,

i have a sheet and want to sum a column with 3 criteria like below:

A B C D
1 date kind1 kind2 amount
2 1/1/2006 silic toronto 10000
3 2/1/2006 Cab Quebec 2000
..
..

i want to sum amounts if dates are less than a date and kind1 is for example
silic and the kind2 is toronto can i use sumif?

any sugestion,

Thanks
--
Farhad Hodjat

=SUMPRODUCT(--(A2:A20<M1),--(B2:B20="silic"),--(C2:C20="toronto"),D2:D20)

where M1 is the testdate.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Farhad" wrote in message
...
Hi all,

i have a sheet and want to sum a column with 3 criteria like below:

A B C D
1 date kind1 kind2 amount
2 1/1/2006 silic toronto 10000
3 2/1/2006 Cab Quebec 2000
.
.

i want to sum amounts if dates are less than a date and kind1 is for
example
silic and the kind2 is toronto can i use sumif?

any sugestion,

Thanks
--
Farhad Hodjat

Nope to =sumif()

But you could use:

=sumproduct(--(a1:a100<date(2006,12,1)),--(b1:b100="silic"),
--(c1:c100="toronto"),(d1:d100))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Farhad wrote:

Hi all,

i have a sheet and want to sum a column with 3 criteria like below:

A B C D
1 date kind1 kind2 amount
2 1/1/2006 silic toronto 10000
3 2/1/2006 Cab Quebec 2000
.
.

i want to sum amounts if dates are less than a date and kind1 is for example
silic and the kind2 is toronto can i use sumif?

any sugestion,

Thanks
--
Farhad Hodjat

--

Dave Peterson

Here it is :-) enjoy!!
A B C D
date kind1 kind2 amount
1/1/2007 slic toronto 2
1/1/2005 slic toronto 1
1/2/2007 cab quebec 1
1/1/2007 slic toronto 2
1/4/2007 slic quebec 1
1/5/2007 cab quebec 1

1/1/2007 slic toronto 2 in this box you write:

6 =SUMPRODUCT(--(A2:A8DATE(2006,1,1)),--(B2:B8="slic"),--(C2:C8="toronto"), D2:D8)






"Farhad" wrote in message ...
Hi all,

i have a sheet and want to sum a column with 3 criteria like below:

A B C D
1 date kind1 kind2 amount
2 1/1/2006 silic toronto 10000
3 2/1/2006 Cab Quebec 2000
.
.

i want to sum amounts if dates are less than a date and kind1 is for example
silic and the kind2 is toronto can i use sumif?

any sugestion,

Thanks
--
Farhad Hodjat

Thakns Dave & Bob
--
Farhad Hodjat


"Farhad" wrote:

Hi all,

i have a sheet and want to sum a column with 3 criteria like below:

A B C D
1 date kind1 kind2 amount
2 1/1/2006 silic toronto 10000
3 2/1/2006 Cab Quebec 2000
.
.

i want to sum amounts if dates are less than a date and kind1 is for example
silic and the kind2 is toronto can i use sumif?

any sugestion,

Thanks
--
Farhad Hodjat

I wanted to do something similar, and it does not sum it up.
Can you please help.
Thanks in advance

=SUMPRODUCT( --(C1:C10 1), --(C1:C10 < 2), D1:D10 )
C D
1.1 3
1.2 2
1.3 1
2.2 3
2.1 1


"Dave Peterson" wrote:

Nope to =sumif()

But you could use:

=sumproduct(--(a1:a100<date(2006,12,1)),--(b1:b100="silic"),
--(c1:c100="toronto"),(d1:d100))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Farhad wrote:

Hi all,

i have a sheet and want to sum a column with 3 criteria like below:

A B C D
1 date kind1 kind2 amount
2 1/1/2006 silic toronto 10000
3 2/1/2006 Cab Quebec 2000
.
.

i want to sum amounts if dates are less than a date and kind1 is for example
silic and the kind2 is toronto can i use sumif?

any sugestion,

Thanks
--
Farhad Hodjat

--

Dave Peterson

Btw, It does work, My bad.
I had an error in one of the other columns.
....

"Shan" wrote:

I wanted to do something similar, and it does not sum it up.
Can you please help.
Thanks in advance

=SUMPRODUCT( --(C1:C10 1), --(C1:C10 < 2), D1:D10 )
C D
1.1 3
1.2 2
1.3 1
2.2 3
2.1 1


"Dave Peterson" wrote:

Nope to =sumif()

But you could use:

=sumproduct(--(a1:a100<date(2006,12,1)),--(b1:b100="silic"),
--(c1:c100="toronto"),(d1:d100))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Farhad wrote:

Hi all,

i have a sheet and want to sum a column with 3 criteria like below:

A B C D
1 date kind1 kind2 amount
2 1/1/2006 silic toronto 10000
3 2/1/2006 Cab Quebec 2000
.
.

i want to sum amounts if dates are less than a date and kind1 is for example
silic and the kind2 is toronto can i use sumif?

any sugestion,

Thanks
--
Farhad Hodjat

--

Dave Peterson