# Formula to Sum all Rents for the current month.

#### USAMax

##### Well-known Member
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!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Try:

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

Change the number in red to the appropriate month.

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):
Code:
``=SUMIFS(E2:E20,C2:C20,"Rent",D2:D20,">=" & DATE(YEAR(TODAY()),MONTH(TODAY()),1),D2:D20,"<=" & DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))``

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

Code:
``[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.

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!

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.

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!

Replies
2
Views
446
Replies
6
Views
293
Replies
3
Views
709
Replies
3
Views
399
Replies
2
Views
165

1,196,510
Messages
6,015,624
Members
441,912
Latest member
Rayna_rahman00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back