Home | Quick Start | Documentation | Download | Support | Contribute

# Aggregators In AFC

Aggregators are functions that compute values over (possibly non-contiguous) ranges of values.

### `SUM` and Range Specifications

I use the `SUM` function to show the types of range specification supported by AFC, as well as the support for `SUM` as such:

##### 1-dim range
 A B C D E 2 6.0 =SUM( C2:E2 ) 1.0 2.0 3.0 3 15.0 ... 4.0 5.0 6.0

##### 2-dim range
 A B C D E 5 21.0 =SUM( C5:E6 ) 1.0 2.0 3.0 6 30.0 =(SUM( C6:E6 ) * 2.0) 4.0 5.0 6.0

##### Single cell
 A B C 8 1.0 =SUM( C8 ) 1.0 9 4.0 ... 4.0

##### Multiple cells
 A B C D 11 3.0 =SUM( C11, D11 ) 1.0 2.0 12 9.0 ... 4.0 5.0

##### Three cells with blanks
 A B C D E 14 2.0 =SUM( C14, D14, E14 ) 2.0 15 10.0 ... 4.0 6.0

##### Non-contiguous cells
 A B C D E F G H I 17 15.0 =SUM( C17, G17, D17, I17 ) 1.0 2.0 3.0 4.0 5.0 6.0 7.0 7.0 18 17.0 ... 7.0 6.0 5.0 4.0 3.0 2.0 1.0 7.0

##### Cells referenced multiply
 A B C D 20 6.0 =SUM( D20, C20, C20, D20 ) 1.0 2.0 21 18.0 ... 4.0 5.0

##### Named range
 A B C D E 23 15.0 =SUM( SumRange ) 4.0 5.0 6.0 24 4.0 =SUM( C24:E24 ) 1.0 3.0

##### Range with blanks
 A B C D E F G H 26 3.0 =SUM( C26:H26 ) 1.0 2.0 27 15.0 ... 4.0 5.0 6.0

##### Mixture of ranges and cells with blanks
 A B C D E F G H I 29 9.0 =SUM( C29:E29, G29, I29 ) 1.0 100.0 200.0 8.0 7.0 30 6.0 ... 2.0 80.0 4.0 7.0

### Other Aggregators Like `SUM`

There are a number of other supported aggregation functions which behave much like `SUM` in the type of arguments they accept. They are:

##### PRODUCT (does not support blanks!)
 A B C D E F G H I 2 6.0 =PRODUCT( C2:E2 ) 1.0 2.0 3.0 3 120.0 ... 4.0 5.0 6.0 5 720.0 =PRODUCT( C5:E6 ) 1.0 2.0 3.0 6 14400.0 =(PRODUCT( C6:E6 ) ^ 2.0) 4.0 5.0 6.0 8 1.0 =PRODUCT( C8 ) 1.0 9 4.0 ... 4.0 11 2.0 =PRODUCT( C11, D11 ) 1.0 2.0 12 20.0 ... 4.0 5.0 14 6.0 =PRODUCT( C14, D14, E14 ) 1.0 2.0 3.0 15 120.0 ... 4.0 5.0 6.0 17 70.0 =PRODUCT( C17, G17, D17, I17 ) 1.0 2.0 3.0 4.0 5.0 6.0 7.0 7.0 18 126.0 ... 7.0 6.0 5.0 4.0 3.0 2.0 1.0 7.0 20 4.0 =PRODUCT( D20, C20, C20, D20 ) 1.0 2.0 21 400.0 ... 4.0 5.0 23 120.0 =PRODUCT( ProductRange ) 4.0 5.0 6.0 24 6.0 =PRODUCT( C24:E24 ) 1.0 2.0 3.0 26 210.0 =PRODUCT( C26:E26, G26, I26 ) 1.0 2.0 3.0 100.0 5.0 200.0 7.0 7.0 27 30240.0 ... 6.0 7.0 8.0 80.0 9.0 10.0 7.0

##### COUNT (does not support blanks!)
 A B C D E F G H I 29 3.0 =COUNT( C29:E29 ) 1.0 2.0 3.0 30 3.0 ... 4.0 5.0 6.0 32 6.0 =COUNT( C32:E33 ) 1.0 2.0 3.0 33 6.0 =(COUNT( C33:E33 ) * 2.0) 4.0 5.0 6.0 35 1.0 =COUNT( C35 ) 1.0 36 1.0 ... 4.0 38 2.0 =COUNT( C38, D38 ) 1.0 2.0 39 2.0 ... 4.0 5.0 41 3.0 =COUNT( C41, D41, E41 ) 1.0 2.0 3.0 42 3.0 ... 4.0 5.0 6.0 44 4.0 =COUNT( C44, G44, D44, I44 ) 1.0 2.0 3.0 4.0 5.0 6.0 7.0 7.0 45 4.0 ... 7.0 6.0 5.0 4.0 3.0 2.0 1.0 7.0 47 4.0 =COUNT( D47, C47, C47, D47 ) 1.0 2.0 48 4.0 ... 4.0 5.0 50 3.0 =COUNT( CountRange ) 4.0 5.0 6.0 51 3.0 =COUNT( C51:E51 ) 1.0 2.0 3.0 53 5.0 =COUNT( C53:E53, G53, I53 ) 1.0 2.0 3.0 100.0 5.0 200.0 7.0 7.0 54 5.0 ... 6.0 7.0 8.0 80.0 9.0 10.0 7.0

##### COUNTA
 A B C D E 56 3.0 =COUNTA( C56:E56 ) 1.0 true 57 3.0 ... false 5.0 Hello

##### AVERAGE (does not support blanks!)
 A B C D E F G H I 59 2.0 =AVERAGE( C59:E59 ) 1.0 2.0 3.0 60 5.0 ... 4.0 5.0 6.0 62 3.5 =AVERAGE( C62:E63 ) 1.0 2.0 3.0 63 5.0 =AVERAGE( C63:E63, C63:E63 ) 4.0 5.0 6.0 65 1.0 =AVERAGE( C65 ) 1.0 66 4.0 ... 4.0 68 1.5 =AVERAGE( C68, D68 ) 1.0 2.0 69 4.5 ... 4.0 5.0 71 2.0 =AVERAGE( C71, D71, E71 ) 1.0 2.0 3.0 72 5.0 ... 4.0 5.0 6.0 74 3.75 =AVERAGE( C74, G74, D74, I74 ) 1.0 2.0 3.0 4.0 5.0 6.0 7.0 7.0 75 4.25 ... 7.0 6.0 5.0 4.0 3.0 2.0 1.0 7.0 77 1.5 =AVERAGE( D77, C77, C77, D77 ) 1.0 2.0 78 4.5 ... 4.0 5.0 80 5.0 =AVERAGE( AverageRange ) 4.0 5.0 6.0 81 2.0 =AVERAGE( C81:E81 ) 1.0 2.0 3.0 83 3.6 =AVERAGE( C83:E83, G83, I83 ) 1.0 2.0 3.0 100.0 5.0 200.0 7.0 7.0 84 8.0 ... 6.0 7.0 8.0 80.0 9.0 10.0 7.0

##### MIN (does not support blanks!)
 A B C D E F G H I 86 -1.0 =MIN( C86:E86 ) -1.0 2.0 -1.0 87 4.0 ... 4.0 5.0 6.0 89 1.0 =MIN( C89:E90 ) 1.0 2.0 3.0 90 4.0 =MIN( C90:E90 ) 4.0 5.0 6.0 92 1.0 =MIN( C92 ) 1.0 93 4.0 ... 4.0 95 1.0 =MIN( C95, D95 ) 1.0 2.0 96 4.0 ... 4.0 5.0 98 1.0 =MIN( C98, D98, E98 ) 1.0 2.0 3.0 99 -6.0 ... -4.0 -5.0 -6.0 101 1.0 =MIN( C101, G101, D101, I101 ) 1.0 2.0 3.0 4.0 5.0 6.0 7.0 7.0 102 1.0 ... 7.0 6.0 5.0 4.0 3.0 2.0 1.0 7.0 104 1.0 =MIN( D104, C104, C104, D104 ) 1.0 2.0 105 4.0 ... 4.0 5.0 107 4.0 =MIN( MinRange ) 4.0 5.0 6.0 108 1.0 =MIN( C108:E108 ) 1.0 2.0 3.0 110 1.0 =MIN( C110:E110, G110, I110 ) 1.0 2.0 3.0 100.0 5.0 200.0 7.0 7.0 111 6.0 ... 6.0 7.0 8.0 80.0 9.0 10.0 7.0

##### MAX (does not support blanks!)
 A B C D E F G H I 113 2.0 =MAX( C113:E113 ) -1.0 2.0 -1.0 114 6.0 ... 4.0 5.0 6.0 116 6.0 =MAX( C116:E117 ) 1.0 2.0 3.0 117 6.0 =MAX( C117:E117 ) 4.0 5.0 6.0 119 1.0 =MAX( C119 ) 1.0 120 4.0 ... 4.0 122 2.0 =MAX( C122, D122 ) 1.0 2.0 123 5.0 ... 4.0 5.0 125 3.0 =MAX( C125, D125, E125 ) 1.0 2.0 3.0 126 -4.0 ... -4.0 -5.0 -6.0 128 7.0 =MAX( C128, G128, D128, I128 ) 1.0 2.0 3.0 4.0 5.0 6.0 7.0 7.0 129 7.0 ... 7.0 6.0 5.0 4.0 3.0 2.0 1.0 7.0 131 2.0 =MAX( D131, C131, C131, D131 ) 1.0 2.0 132 5.0 ... 4.0 5.0 134 6.0 =MAX( MaxRange ) 4.0 5.0 6.0 135 3.0 =MAX( C135:E135 ) 1.0 2.0 3.0 137 7.0 =MAX( C137:E137, G137, I137 ) 1.0 2.0 3.0 100.0 5.0 200.0 7.0 7.0 138 10.0 ... 6.0 7.0 8.0 80.0 9.0 10.0 7.0

##### VARP (does not support blanks!)
 A B C D E F G H I 140 0.0 =VARP( C140 ) 1.0 141 0.0 ... 4.0 143 0.25 =VARP( C143, D143 ) 1.0 2.0 144 0.25 ... 4.0 5.0 146 4.64 =VARP( C146:E146, G146, I146 ) 1.0 2.0 3.0 100.0 5.0 200.0 7.0 7.0 147 2.0 ... 6.0 7.0 8.0 80.0 9.0 10.0 7.0

##### VAR (does not support blanks!)
 A B C D E F G H I 149 0.5 =VAR( C149, D149 ) 1.0 2.0 150 0.5 ... 4.0 5.0 152 5.8 =VAR( C152:E152, G152, I152 ) 1.0 2.0 3.0 100.0 5.0 200.0 7.0 7.0 153 2.5 ... 6.0 7.0 8.0 80.0 9.0 10.0 7.0

##### STDEV (does not support blanks!)
 A B C D E F G H I 155 2.8284271247461903 =STDEV( C155, D155 ) -1.0 3.0 156 2.0816659994661326 =STDEV( C156, D156, E156 ) -1.0 0.0 3.0 158 40.688718279617696 =STDEV( C158:E158, F158:H158, I158 ) -1.0 0.0 5.45 7.62 12.7 14.5 -100.0 7.0 159 4.15187851918806 ... -10.0 0.0 1.0 0.0 -1.0 -2.0 3.0 7.0

##### STDEVP (does not support blanks!)
 A B C D E F G H I 161 0.0 =STDEVP( C161 ) 7.0 162 1.5 =STDEVP( C162, D162 ) 2.0 5.0 163 2.0548046676563256 =STDEVP( C163, D163, E163 ) 2.0 0.0 5.0 165 37.6704332172529 =STDEVP( C165:E165, F165:H165, I165 ) -1.0 0.0 5.45 7.62 12.7 14.5 -100.0 7.0 166 3.843892584878203 ... -10.0 0.0 1.0 0.0 -1.0 -2.0 3.0 7.0

##### AVEDEV (does not support blanks!)
 A B C D E F G H I 168 0.0 =AVEDEV( C168 ) 40.0 169 2.0 =AVEDEV( C169, D169 ) -1.0 3.0 170 1.5555555555555554 =AVEDEV( C170, D170, E170 ) -1.0 0.0 3.0 172 26.092653061224492 =AVEDEV( C172:E172, F172:H172, I172 ) -1.0 0.0 5.45 7.62 12.7 14.5 -100.0 7.0 173 2.693877551020408 ... -10.0 0.0 1.0 0.0 -1.0 -2.0 3.0 7.0

##### DEVSQ (does not support blanks!)
 A B C D E F G H I 175 0.0 =DEVSQ( C175 ) 40.0 176 8.0 =DEVSQ( C176, D176 ) -1.0 3.0 177 8.666666666666668 =DEVSQ( C177, D177, E177 ) -1.0 0.0 3.0 179 9933.430771428571 =DEVSQ( C179:E179, F179:H179, I179 ) -1.0 0.0 5.45 7.62 12.7 14.5 -100.0 7.0 180 103.4285714285714 ... -10.0 0.0 1.0 0.0 -1.0 -2.0 3.0 7.0

##### SKEW (does not support blanks!)
 A B C D E F G H I 182 0.9352195295828235 =SKEW( C182, D182, E182 ) 1.0 2.0 4.0 184 -2.532706643504132 =SKEW( C184:E184, F184:H184, I184 ) -1.0 0.0 5.45 7.62 12.7 14.5 -100.0 7.0 185 -1.8491263727532468 ... -10.0 0.0 1.0 0.0 -1.0 -2.0 3.0 7.0

##### KURT (does not support blanks!)
 A B C D E F G H I 187 0.3905325443786989 =KURT( C187, D187, E187, F187 ) 1.0 2.0 4.0 -1.0 189 6.5478454980828005 =KURT( C189:E189, F189:H189, I189 ) -1.0 0.0 5.45 7.62 12.7 14.5 -100.0 7.0 190 4.2380757608131585 ... -10.0 0.0 1.0 0.0 -1.0 -2.0 3.0 7.0

##### SUMSQ
 A B C D E F G H I 192 49.0 =SUMSQ( C192 ) 7.0 193 29.0 =SUMSQ( C193, D193 ) 2.0 5.0 194 29.0 =SUMSQ( C194, D194, E194 ) 2.0 5.0 196 10460.3069 =SUMSQ( C196:E196, F196:H196, I196 ) -1.0 0.0 5.45 7.62 12.7 14.5 -100.0 7.0 197 115.0 ... -10.0 1.0 0.0 -1.0 -2.0 3.0 7.0

##### COVAR
 A B C D E F G H 199 0.6666666666666666 =COVAR( C199:E199, F199:H199 ) 1.0 2.0 3.0 4.0 5.0 6.0 200 -0.1111111111111111 ... 2.0 3.0 2.0 2.0 2.0 3.0 202 0.25 =COVAR( C202:D202, E202:F202 ) 1.0 2.0 3.0 4.0 203 !NA =COVAR( C203:E203, F203:G203 ) 2.0 3.0 5.0 4.0 5.0 Excel says: #N/A

##### SUMIF
 A B C D E F G H I 205 43.0 =SUMIF( D205:F205, (">" & C205), G205:I205 ) 10.0 10.0 11.0 12.0 20.0 21.0 22.0 7.0 206 23.0 =SUMIF( D206:F206, (">" & C206) ) 10.0 10.0 11.0 12.0