Trying to use SUMIFS to sum a column based on two conditions

Hello all!

I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving.

Here is my formula that works so far.
=SUMIF(Data!C:C,"Office",Data!D:D)

Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format.
1/9/2010 9:21:49 AM

I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS.

=SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates")

Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.

All help is appreciated, let me know what you think. Oh and Data! is the worksheet in case that throws anyone off.

I am using 2007 to work on the statement, but I will be using the formula on Google docs spreadsheet. And you are right, Google Spreadsheet does not recognize SUMIFS as a function. I will have to rework this...

Thanks for all of your replies. For some reason SUMPRODUCT did seem like the most logical function to use. But it is an array function and Google Spreadsheet does not have SUMIFS function built in yet. I'll have to go with sweeps answer as the best for this situation.

Thanks everyone.

Yes I did notice. I am looking at the ins and outs right now.

The function is returning zeros. But think that is because of the date condition. The cell that I am working with is not a date formatted cell. But really just a text time stamp of when the information was submitted. I think I might need a little help on that.

If possible, could someone also post the name of the DATE(2010,1,1) condition. I want to Google more of these formatted condition statements.

Thanks

Sorry, yes a file does speak a thousand posts. The file is attached.

Thanks again for your support.

I have been playing with the formula for about an hour and I am still stuck.

Here is a link to the Google Spreadsheet I am testing this on.
Google Spreadsheet

I have worked through the formula all the way through a number of times and it looks like it should work. But I only get a 0.

DonkeyOte mentioned that this formula would not work on pre 2007 excel, but I cannot figure out why?

All help is appreciated.

OK I'm not an expert on Google docs but I seem to recall that SUMPRODUCT doesn't work very well on conditional counts/sums you probably need an "array formula". Try this

=ArrayFormula(SUM(If((Data!C2:C100="Office2")*(Data!A2:A100<DATE(2010,1,14))*(Data!A2:A100>DATE(2010,1,1)),Data!D2:D100)))

Note: I can't open your attachment, it's asking for a password....

Sorry about the link. Lets try this one, you shouldn't have to sign in with this link.

Google Spreadsheet

I am checking out the formula now.

Thanks