Blank Cells Again

[Josh Hendrickson]

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!

 

[Max]

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$10)
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

 

[ShaneDevenshire]

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)

 

[Max]

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

 

[Ashish Mathur]

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