Sumif array formula with more than 2 criteria

[kcash]

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:

=SUM(IF((Account!$G$103:$G$3004=D1)*(Account!$H$103:$H$3004="Advertising"),Account!$F$103:$F$3004)).

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.

 

[Aladin Akyurek]

Better:

[1]

=SUM(IF(Account!$G$103:$G$3004=D1,IF(Account!$H$103:$H$3004="Advertising",Account!$F$103:$F$3004))

or, alternatively...

[2]

=SUMPRODUCT(--(Account!$G$103:$G$3004=D1),--(Account!$H$103:$H$3004="Advertising"),Account!$F$103:$F$3004)

which needs just enter.

Adding a date range...

To [1]...

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

To [2]...

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

where Date1 <= Date2.

 

[kcash]

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.

=SUM(IF(Account!$G$103:$G$3004=D1,IF(Account!$H$103:$H$3004="Advertising",Account!$F$103:$F$3004))IF(account!$b$103:$b$3000>=$T$1),IF(account!$b$103:$b$3000<=$T$2)

 

[Aladin Akyurek]

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.

=SUM(IF(Account!$G$103:$G$3004=D1,IF(Account!$H$103:$H$3004="Advertising",Account!$F$103:$F$3004))IF(account!$b$103:$b$3000>=$T$1),IF(account!$b$103:$b$3000<=$T$2)

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

=SUM(IF(Account!$G$103:$G$3004=D1,IF(Account!$H$103:$H$3004="Advertising",IF(Account!$b$103:$b$3004-DAY(Account!$b$103:$b$3004)+1=$T$1,Account!$F$103:$F$3004))))

which is confirmed with control+shift+enter.

 

[kcash]

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

 

[Aladin Akyurek]

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

=SUM(IF(Account!$G$103:$G$3004=D1,IF(Account!$H$103:$H$3004="Advertising",IF(Account!$b$103:$b$3004>=$T$1,IF(Account!$b$103:$b$3004<=$T$2,Account!$F$103:$F$3004)))))

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