# Sumif array formula with more than 2 criteria

#### kcash

##### New Member
Here is my dilema,

I am attempting to create a full accounting spreadsheet for our rental property business. I have a check register
sheet in which a enter all receipts/disbursments each with an account and property designation. These figures
are posted to a cash flow sheet which can tell me how much has been spent at each property and with each account.
I came up with this formula off of some postings I found on your website:

This is copied over to the other "intersections" on the cash flow sheet and works great.

The problem is that I have realised that I am going to also need to incorporate a date range into this formula.
My plan is to set up 12 worksheets in order to have 12 monthly cash flow reports. Even better would be to keep
the one cash flow report and have two cells with dates entered that would be referenced by the formula for the date
range. If I haven't confused you, any help would be greatly appreciated.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

##### MrExcel MVP
Better:

[1]

or, alternatively...

[2]

which needs just enter.

To [1]...

...,IF(DateRange>=Date1,IF(DateRange<=Date2,...

To [2]...

...,--(DateRange>=Date1),--(DateRange<=Date2),...

where Date1 <= Date2.

#### kcash

##### New Member
Thanks for the quick reply! Based on what you told me, this is what i have put together, the T1 and T2 contain the date values (currently t1 is 1-1-06 and t2 is 1-31-06). I am getting an error message, this looks very close to what I am needing.

##### MrExcel MVP
Thanks for the quick reply! Based on what you told me, this is what i have put together, the T1 and T2 contain the date values (currently t1 is 1-1-06 and t2 is 1-31-06). I am getting an error message, this looks very close to what I am needing.

T1 will suffice for you are interested in a month/year interval:

which is confirmed with control+shift+enter.

#### kcash

##### New Member
Yes, that works, one issue though, I do need the two date ranges because I must be able to do year to date as well. I currently have it running on two worksheets, one with the year to date and one with the monthly formula you just helped me create. I noticed a significant increase in the calculation time and I should probably make it just one sheet. Could you show me the same formula with a date from (t1) and date to (t2) condition? I promise to quit bugging you after this.

Much Thanks!
KC

##### MrExcel MVP
Yes, that works, one issue though, I do need the two date ranges because I must be able to do year to date as well. I currently have it running on two worksheets, one with the year to date and one with the monthly formula you just helped me create. I noticed a significant increase in the calculation time and I should probably make it just one sheet. Could you show me the same formula with a date from (t1) and date to (t2) condition? I promise to quit bugging you after this.

Much Thanks!
KC

You'll certainly run up against the performance problem if you implement such a formula in a huge number of cells.

Replies
5
Views
534
Replies
5
Views
261
Replies
1
Views
255
Replies
1
Views
136
Replies
1
Views
187

1,141,095
Messages
5,704,311
Members
421,338
Latest member
Pepess

### 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