Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum
=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sum
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |