Multiple Criteria for SUMIF

  • Thread starter Thread starter Duncan
  • Start date Start date
D

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.
 
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
 
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:A1000>TODAY()-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
 
Back
Top