Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Cells Again
Thanks for everyone's help, here is the problem I am trying to solve
I have two sheets within a workbook; On sheet one I have three columns; Column A has a list of dates, Column B has a list of dollar amounts and Column C has random words It looks somewhat like this Column A Column B Column C 10/24/2008 $100 Dog 10/23/2008 $220 Cat 10/20/2008 $100 10/19/2008 $115 Dog 10/19/2008 $230 10/18/2008 $115 Mouse 10/16/2008 $300 On the second sheet I have a list of dates and I want to find out the total revenue from column B on the first sheet between two dates, say 10/19/2008 and 10/23/2008 and that have words written in column C. I then want to do the same thing with things that don't have words written in Column C. Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Cells Again
In Sheet2,
Assume Startdate/Enddate inputs are in A2:B2 down In B2: =SUMPRODUCT((Sheet1!$A$2:$A$10=A2)*(Sheet1!$A$2:$ A$10<=B2)*(Sheet1!$C$2:$C$10<""),Sheet1!$B$2:$B$1 0) Copy B2 down. Adapt the ranges to suit. P/s: Pl mark/rate all responses which help by pressing the Yes buttons (like the ones below). You should do that in your earlier thread as well. -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- "Josh Hendrickson" wrote: Thanks for everyone's help, here is the problem I am trying to solve I have two sheets within a workbook; On sheet one I have three columns; Column A has a list of dates, Column B has a list of dollar amounts and Column C has random words It looks somewhat like this Column A Column B Column C 10/24/2008 $100 Dog 10/23/2008 $220 Cat 10/20/2008 $100 10/19/2008 $115 Dog 10/19/2008 $230 10/18/2008 $115 Mouse 10/16/2008 $300 On the second sheet I have a list of dates and I want to find out the total revenue from column B on the first sheet between two dates, say 10/19/2008 and 10/23/2008 and that have words written in column C. I then want to do the same thing with things that don't have words written in Column C. Any help would be greatly appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Cells Again
Hi,
Or this version that is generally safer: =SUMPRODUCT(--(Sheet2!$A$1:$A$7=C9),--(Sheet2!$A$1:$A$7<=C10),--(Sheet2!$C$1:$C$7<""),Sheet2!$B$1:$B$7) or not, then this one that is shorter: =SUMPRODUCT((Sheet2!A$1:A$7=C9)*(Sheet2!A$1:A$7<= C10)*(Sheet2!C$1:C$7<"")*Sheet2!B$1:B$7) and new in 2007, use the SUMIFS function: =SUMIFS(Sheet1!B$1:B$7,Sheet1!C$1:C$7,"<",Sheet1! A$1:A$7,"="&A1,Sheet1!A$1:A$7,"<="&A2) -- Thanks, Shane Devenshire "Josh Hendrickson" wrote: Thanks for everyone's help, here is the problem I am trying to solve I have two sheets within a workbook; On sheet one I have three columns; Column A has a list of dates, Column B has a list of dollar amounts and Column C has random words It looks somewhat like this Column A Column B Column C 10/24/2008 $100 Dog 10/23/2008 $220 Cat 10/20/2008 $100 10/19/2008 $115 Dog 10/19/2008 $230 10/18/2008 $115 Mouse 10/16/2008 $300 On the second sheet I have a list of dates and I want to find out the total revenue from column B on the first sheet between two dates, say 10/19/2008 and 10/23/2008 and that have words written in column C. I then want to do the same thing with things that don't have words written in Column C. Any help would be greatly appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Cells Again
Typo error in earlier description, sorry
The formula is to be in C2 (not B2), then copied down -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Blank Cells Again
Hi,
You may also try this array formula (Ctrl+Shift+Enter) SUM(IF((A1:A7=A9)*(A1:A7<=A10)*(C1:C7<""),B1:B7) ) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Josh Hendrickson" wrote in message ... Thanks for everyone's help, here is the problem I am trying to solve I have two sheets within a workbook; On sheet one I have three columns; Column A has a list of dates, Column B has a list of dollar amounts and Column C has random words It looks somewhat like this Column A Column B Column C 10/24/2008 $100 Dog 10/23/2008 $220 Cat 10/20/2008 $100 10/19/2008 $115 Dog 10/19/2008 $230 10/18/2008 $115 Mouse 10/16/2008 $300 On the second sheet I have a list of dates and I want to find out the total revenue from column B on the first sheet between two dates, say 10/19/2008 and 10/23/2008 and that have words written in column C. I then want to do the same thing with things that don't have words written in Column C. Any help would be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Quantity Blank, Remaining cells in row appear Blank | Excel Worksheet Functions | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
Making Blank Cells Really Blank (Zen Koan) | Excel Worksheet Functions |