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!

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!

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!

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
---

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!