SumIF --- Different than below SumIF

[Chuck_Wagon]

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!

 

[Oaktree]

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]

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]

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]

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]

Thanks for all the info works great!!!!!