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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,105
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,105
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,181,785
Messages
5,932,039
Members
436,816
Latest member
Composh

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