Aggregate Function Using Sum

**gtbaseball7** · 07-01-2015, 11:29 AM

I am trying to add one row if the another row is not 0. I am using the Aggregate function. Here is what I have and I'm getting a #VALUE! error.

=AGGREGATE(9,6,$K622:$DG622/($K258:$DG258<>0))

Also, what is the default [k] value for this function? I've tried default and using 1. Not quite clear on what [k] means...

Thanks!

**Glenn Kennedy** · 07-01-2015, 12:03 PM

Try this (array entered : CTRL-SHIFT-ENTER)
=IF($K258:$DG258<>0,AGGREGATE(9,6,$K622:$DG622))

or (non-array)
=SUMIF($K258:$DG258,"<>0",$K622:$DG622)

**daddylonglegs** · 07-01-2015, 12:12 PM

Originally Posted by gtbaseball7

Also, what is the default [k] value for this function? I've tried default and using 1. Not quite clear on what [k] means...

There isn't a k value when you use 9 (sum). That only applies when it fits the function in question, I.e with functions 14 to 19, SMALL, LARGE, PERCENTILE and QUARTILE

**gtbaseball7** · 07-01-2015, 02:19 PM

Daddy LL-

Thank for your response. Why would the AGGREGATE function as I have it set up, return a value error?

You are normally well informed when it comes to the AGGREGATE function. I prefer not to use Array formulas as I am not the only one accessing this file. I hope you can help me understand. Thanks.