Formula to Sum all Rents for the current month.

[USAMax]

I tried to figure how to use a SumIf but that did not work.

All the dates are in column D and the item description that says Rent is in column C. The value to be summed is in column E.

Sample data for C, D and E:
Rent, 01/02/15, $400.00
Rent, 01/05/15, $375.00
Mgt Fees, 01/08/15, $62.00
Rent, 02/06/15, $500.00
Mgt Fees, 02/06/15, $40.00
Late Fees, 02/18/15, $275.00
Mgt Fees, 02/18/15, $47.00
Leasing Fee, 02/18/15, $150.00
Rent, 03/10/15, $400.00
Mgt Fees, 03/13/15, $32.00
Late Fees, 03/30/15, $50.00
Late Fees, 03/30/15, $375.00
Mgt Fees, 03/31/15, $55.00
Taxes, 03/25/15, $45.83

I don't think that it maters but this formula will go into column H.

Thank you!

 

[bbott]

Try:

=SUMPRODUCT(--(C2:C20="Rent"),--(MONTH(D2:D20)=1),E2:E20)

Change the number in red to the appropriate month.

 

[Joe4]

If, as your title suggests, you want it to always work for the current month, you can use this formula, which is dynamic (so you never need to adjust the dates):

=SUMIFS(E2:E20,C2:C20,"Rent",D2:D20,">=" & DATE(YEAR(TODAY()),MONTH(TODAY()),1),D2:D20,"<=" & DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

 

[Scott Huish]

=SUMPRODUCT(--(C1:C15="Rent"),--(MONTH(D1:D15)=MONTH(TODAY())),E1:E15)

 

[Joe4]

[COLOR=#333333]=SUMPRODUCT(--(C1:C15="Rent"),--(MONTH(D1:D15)=MONTH(TODAY())),E1:E15)[/COLOR]

Scott,

You probably want to add another condition, to check for the Year. Otherwise, if they have more than one years worth of data, you will also be returning records from the current month of last year.

 

[USAMax]

Wow! You guys are amazing... I put bbot's formula into action and I applied the month formula that I already had. Before I could come back and thank him I have so many other ways I can deal with the issue.

Thank you everyone!

 

[Joe4]

You are welcome.

Just bear in mind that if your data spans more than one year, you will need to make modifications to bbot's and Scott's solution to account for the year too, or else it will not work correctly. Its not hard, just add one more condition to each, which mirrors the Month logic, but does it for Year.

 

[USAMax]

Thank you Joe4, I was so excited to see the solution that took the year into consideration but I have one page for each year do that is not an issue.

I say it again and again, you guys are amazing!