# SumIF --- Different than below SumIF

#### Chuck_Wagon

##### New Member
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!

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

#### Chuck_Wagon

##### New Member
Thanks for all the info works great!!!!!

Replies
3
Views
95
Replies
0
Views
79
Replies
30
Views
567
Replies
4
Views
98
Replies
21
Views
470

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.

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