# Thread: Formula to Counting Customer Sums

1. ## Formula to Counting Customer Sums

Example file attached. I am trying to count the number customers in 2017 that total for 2016+2017 fall into the threasholds in A26 to A32 (greater than or equal to the amount, but less than value above) without adding a helper column.

For example, at the 80k level, the formula would return three customers as customer I-K totals are >= 80k, but < 100k.

The full result would be
100k - 5
80k - 3
50k -8
36k - 0
25k - 0
12.5k - 0
1 - 1

2. Out of curiosity, why do not you want an extra column (which you can hide)?

Please specify which customers belong to the level.

- 100.000 (?, ?, ?, ?, ?)
- 80.000 (I, J, K)
- 50.000 (?, ?, ?, ?, ?, ?, ?, ?)
- 1 (?)

3. Originally Posted by navic
Out of curiosity, why do not you want an extra column (which you can hide)?
- We update this data often for new customers/years & have many more years of data/customer that we are consolidating. Trying to get the formula that I can repeat for many columns.

Originally Posted by navic
Please specify which customers belong to the level.
- 100.000 (B, D, E, G, H)
- 80.000 (I, J, K)
- 50.000 (L-S)
-25.000 (F)
- 1 (C)

4. In the level 50000, in the first topic you expect result 8, but now result is L and S customers (2).
In the level 25000, in the first topic you expect result 0, but now result is reported at F customer (1).
I'm sorry but I can not understand your logic? Especially that you do not want the auxiliary column.
Good luck.

5. L-S represents all the numbers from L to S. I just missed one at level 25k in my original summary.

6. Originally Posted by sfaheather84
I just missed one at level 25k in my original summary.

7. Another solution
Code:
```=SUMPRODUCT((\$A\$4:\$A\$22<>"")*(\$B\$4:\$C\$22>=A26)*(\$B\$4:\$C\$22<A25))
or
=SUMPRODUCT(--(COUNTIF(\$A\$4:\$A\$22;\$A\$4:\$A\$22)=1)*(\$B\$4:\$C\$22>=A26)*(\$B\$4:\$C\$22<A25))```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•