Sumif array formula with more than 2 criteria

kcash

New Member
Joined
Oct 16, 2006
Messages
3
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.
 

Excel Facts

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 16, 2006
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 16, 2006
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top