date cost $ sales $
01/21/09 $4.00 $8.00
01/21/09 $7.00
02/21/09 $5.00 $8.00
02/28/09 $3.00

what formula can I use to look at the "date" column, select a time period
(i.e 01/01/09-01/31/09) and sum the the corresponding "Sales $" only if the
"cost $" is <"" ?

thanks

=sumproduct(--(text(a1:a10,"yyyymm")="200901"),--(b1:b10<""),c1:c10)

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

=======
And if you're using xl2007, you may want to look at =sumifs() in excel's help.

joe@malvern wrote:

date cost $ sales $
01/21/09 $4.00 $8.00
01/21/09 $7.00
02/21/09 $5.00 $8.00
02/28/09 $3.00

what formula can I use to look at the "date" column, select a time period
(i.e 01/01/09-01/31/09) and sum the the corresponding "Sales $" only if the
"cost $" is <"" ?

thanks

--

Dave Peterson

Try this:

Use cells to hold the date boundaries.

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A10=E1),--(A1:A10<=F1),--(B1:B10<""),C1:C10)

--
Biff
Microsoft Excel MVP


"joe@malvern" wrote in message
...
date cost $ sales $
01/21/09 $4.00 $8.00
01/21/09 $7.00
02/21/09 $5.00 $8.00
02/28/09 $3.00

what formula can I use to look at the "date" column, select a time period
(i.e 01/01/09-01/31/09) and sum the the corresponding "Sales $" only if
the
"cost $" is <"" ?

thanks

Something like:

=SUMPRODUCT(--(A1:A100DATEVALUE("1/1/2009"))*--(A1:A100<DATEVALUE("1/31/2009"))*--(B1:B100<""),(C1:C100))

--
Gary''s Student - gsnu200842


"joe@malvern" wrote:

date cost $ sales $
01/21/09 $4.00 $8.00
01/21/09 $7.00
02/21/09 $5.00 $8.00
02/28/09 $3.00

what formula can I use to look at the "date" column, select a time period
(i.e 01/01/09-01/31/09) and sum the the corresponding "Sales $" only if the
"cost $" is <"" ?

thanks

Dave...worked perfect...thanks!!

"Dave Peterson" wrote:

=sumproduct(--(text(a1:a10,"yyyymm")="200901"),--(b1:b10<""),c1:c10)

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

=======
And if you're using xl2007, you may want to look at =sumifs() in excel's help.

joe@malvern wrote:

date cost $ sales $
01/21/09 $4.00 $8.00
01/21/09 $7.00
02/21/09 $5.00 $8.00
02/28/09 $3.00

what formula can I use to look at the "date" column, select a time period
(i.e 01/01/09-01/31/09) and sum the the corresponding "Sales $" only if the
"cost $" is <"" ?

thanks

--

Dave Peterson