Multiple Criteria for SUMIF
 

Hello,
I'm having issues trying to sum data using more than a single criteria.
My layout is as such;
Col.A = Date
Col.B = Type
Col.C = Note
Col.D = Value

I am trying to sum all the Values within a single month (Jan, Feb, etc) of a
single type (Rent, Misc, Ent, etc).

I would also like to query the last 90 days (NOW()-90) for each Type.

What would be a good formula for these?

Thanks in advance.
--
<Insert witty comment here

Multiple Criteria for SUMIF
 

=sumproduct((month(a2:a22)=1)*(b2:b22="whattype")* d2:d22)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Duncan" wrote in message
...
Hello,
I'm having issues trying to sum data using more than a single criteria.
My layout is as such;
Col.A = Date
Col.B = Type
Col.C = Note
Col.D = Value

I am trying to sum all the Values within a single month (Jan, Feb, etc) of
a
single type (Rent, Misc, Ent, etc).

I would also like to query the last 90 days (NOW()-90) for each Type.

What would be a good formula for these?

Thanks in advance.
--
<Insert witty comment here

Multiple Criteria for SUMIF
 

A Pivot Table would be best for your summing all Values within different categories. You could also
use the pivot table for the last 90 days, by using a column of formulas in your table based on the
date values:

=A2(TODAY()-90)

and then using that column as one of the Row Fields in your Pivot table.

If you do not want to use a pivot table, then you could use SUMPRODUCT, but at the cost of
convenience, since you would need to write a formula for each, or set up a table of formulas with
headers and row labels (which is exactly what a pivot table handles so very well):

=SUMPRODUCT((MONTH(Dates)=2)*(Type="Rent")*Value)

HTH,
Bernie
MS Excel MVP


"Duncan" wrote in message
...
Hello,
I'm having issues trying to sum data using more than a single criteria.
My layout is as such;
Col.A = Date
Col.B = Type
Col.C = Note
Col.D = Value

I am trying to sum all the Values within a single month (Jan, Feb, etc) of a
single type (Rent, Misc, Ent, etc).

I would also like to query the last 90 days (NOW()-90) for each Type.

What would be a good formula for these?

Thanks in advance.
--
<Insert witty comment here

Multiple Criteria for SUMIF
 

Assuming you do not have Excel 2007:
=SUMPROUDCT(--(MONTH(A1:A1000)=1),--(B1:B1000="Rent"), D1:D1000)
gives sum of rent in January
=SUMPROUDCT(--(A1:A1000TODAY()-90),--(B1:B1000="Rent"), D1:D1000)
should give last 90 days (might be 89 or 91 but you need to test)

And then there are Pivot Tables.....
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Duncan" wrote in message
...
Hello,
I'm having issues trying to sum data using more than a single criteria.
My layout is as such;
Col.A = Date
Col.B = Type
Col.C = Note
Col.D = Value

I am trying to sum all the Values within a single month (Jan, Feb, etc) of
a
single type (Rent, Misc, Ent, etc).

I would also like to query the last 90 days (NOW()-90) for each Type.

What would be a good formula for these?

Thanks in advance.
--
<Insert witty comment here

Multiple Criteria for SUMIF
 

Thank you very much.
I knew it would be something simple I was overlooking.
--
<Insert witty comment here


"Don Guillett" wrote:


=sumproduct((month(a2:a22)=1)*(b2:b22="whattype")* d2:d22)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Duncan" wrote in message
...
Hello,
I'm having issues trying to sum data using more than a single criteria.
My layout is as such;
Col.A = Date
Col.B = Type
Col.C = Note
Col.D = Value

I am trying to sum all the Values within a single month (Jan, Feb, etc) of
a
single type (Rent, Misc, Ent, etc).

I would also like to query the last 90 days (NOW()-90) for each Type.

What would be a good formula for these?

Thanks in advance.
--
<Insert witty comment here

Multiple Criteria for SUMIF
 

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Duncan" wrote in message
...
Thank you very much.
I knew it would be something simple I was overlooking.
--
<Insert witty comment here


"Don Guillett" wrote:


=sumproduct((month(a2:a22)=1)*(b2:b22="whattype")* d2:d22)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Duncan" wrote in message
...
Hello,
I'm having issues trying to sum data using more than a single criteria.
My layout is as such;
Col.A = Date
Col.B = Type
Col.C = Note
Col.D = Value

I am trying to sum all the Values within a single month (Jan, Feb, etc)
of
a
single type (Rent, Misc, Ent, etc).

I would also like to query the last 90 days (NOW()-90) for each Type.

What would be a good formula for these?

Thanks in advance.
--
<Insert witty comment here