SumIF --- Different than below SumIF

Chuck_Wagon

New Member
Joined
Jan 27, 2005
Messages
11
I'm using Sumif to summarize data based on certain Cell values... It works awesome on 3 levels BUT when i include the 4th level I get a property array formula Error ... Is there a setting in the properties that can be changed for 4 level summations???

All data and array sizes have been validated

Thank You!

:oops:
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Can you post a sample of your data (see download Colo's HTML maker at the bottom of this sheet) and the formula you were previously using?
 

Chuck_Wagon

New Member
Joined
Jan 27, 2005
Messages
11
here is the 3 way code ... =SUM(IF('2004'!$B$2:$B$291=A5,IF('2004'!$A$2:$A$291=$C$3,'2004'!$C$2:$C$291,0),0))

in the 4 way i just need to add 1 more if condition. here is sample data.

Channel Week_ending Refi_Flag Status app_totals Loan_amt Scored Credit score
HLD 1/3/2004 0 Applied 1 260000 1 752
HLD 1/3/2004 0 Approved 2 811920 2 1507
HLD 1/3/2004 1 Applied 2 358400 2 1430
HLD 1/3/2004 1 Approved 6 1020450 6 4206

I need to summize app_totals at the following level by channel, week ending, refi_flag, and status.[/list][/list][/code]
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Two suggestions:

1) =sumproduct(--('2004'!$B$2:$B$291=A5),--('2004'!$A$2:$A$291=$C$3),$C$2:$C$291) will be faster than your array formula. You can update this by adding a 4th range such as =sumproduct(--('2004'!$B$2:$B$291=A5),--('2004'!$A$2:$A$291=$C$3),--('2004'!$D$2:$D$291="my new value"),$C$2:$C$291)

2) Have you considered a pivot table?
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
Try the following syntax and just amend the arguments to suit your data:-

=SUMPRODUCT(--('2004'!$B$2:$B$291=A5),--('2004'!$A$2:$A$291=$C$3),--('2004'!$C$2:$C$291=0),--('2004'!$E$2:$E$291=2),'2004'!$F$2:$F$291)

and so on

If the last argument is a set of numbers to be totalled based on the previous arguments then lave as is, but if it is a conditional like the first few arguments then precede it with -- like the others.
 

Forum statistics

Threads
1,148,526
Messages
5,747,216
Members
424,069
Latest member
kamkwok1hh

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