Multiple Criteria for SumIf

[Ronbo]

I would like to sum the total of a category between two dates. There are a
number of posts but I can't seem to get it to work. I created the following;

SUMIF('YTD Data'!D,M3,'YTD Data'!B:B)&SUMIF('YTD Data'!A:A,">="&M1,'YTD
Data'!B:B)-SUMIF('YTD Data'!A:A,">"&M2,'YTD Data'!B:B)

But it does not work very good because the sum is "text", it can not be
formated and if 0 it gives a "text value".

Any help is appreciated.
Ronbo

 

[Bob Phillips]

=SUMIF('YTD Data'!A:A,">="&M1,'YTD Data'!B:B)-
SUMIF('YTD Data'!A:A,">"&M2,'YTD Data'!B:B)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ronbo]

Bob -

I tried that before as part of the formula but it does not give the correct
answer. Secondly, I don't see how this can sum a category.

Example:
A B C

4/1/08 25.00 Food
4/15/08 50.00 Gas
4/20/08 25.00 Misc
4/30/08 50.00 Food

I need a formula that will sum the category(ies) (Food) between two dates
(4/1/08 to 4/30/08) = 75.

Thanks for your help.

 

[Bob Phillips]

It makes more sense now

=SUMPRODUCT(--('YTD Data'!A1:A1000>=M1),--('YTD Data'!A1:A1000<M2),),--('YTD
Data'!C1:C1000="Food"),'YTD Data'!B1:B1000)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ronbo]

Bob -

Thanks a lot, that was exactly what I was looking for. Sorry for the poor
(lazy) question.

 

[Monomeeth]

Hi Bob

I'm pretty much trying to do the same thing with the following formula:

=SUMPRODUCT(--('Invoice Tracker'!A2:A2000>=F1),--('Invoice
Tracker'!A2:A2000<=F2),),--('Invoice Tracker'!F2:F2000,"Dialogue
Days",'Invoice Tracker'!E2:E2000)

Unfortunately, Excel doesn't like it and I can't seem to figure out why?

What I'm trying to do is return the $ value of all rows in the Invoice
Tracker worksheet which meet the following two criteria:

1. the date recorded in the range of A2:A2000 is within a certain date
range, and
2. the program listed in the range of F2:F2000 is Dialogue Days

The dollar amounts are also in the Invoice Tracker worksheet in the range of
E2:E2000.

F1 is the start date and F2 is the end date, both cells being in the current
worksheet (i.e. Budget Tracker).

Any ideas of where I'm going wrong? I'm stuck! By the way, I'm running Excel
2003.

Thanks,

Joe.

 

[Bob Phillips]

You've got your brackets mixed up, and you are comparing the value in the
last condition

=SUMPRODUCT(--('Invoice Tracker'!A2:A2000>=F1),--('Invoice
Tracker'!A2:A2000<=F2),
--('Invoice Tracker'!F2:F2000="Dialogue Days"),'Invoice Tracker'!E2:E2000)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Monomeeth]

Thanks Bob. That's perfect.