Multiple Criteria for SUMIF

[Duncan]

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.

 

[Don Guillett]

=sumproduct((month(a2:a22)=1)*(b2:b22="whattype")*d2:d22)

 

[Bernie Deitrick]

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

 

[Bernard Liengme]

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

And then there are Pivot Tables.....
best wishes

 

[Duncan]

Thank you very much.
I knew it would be something simple I was overlooking.

 

[Don Guillett]

Glad to help