Formula to Counting Customer Sums

sfaheather84

New member
Joined
Jun 13, 2019
Messages
3
Reaction score
0
Points
0
Excel Version(s)
Office 365 MOS
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
 

Attachments

  • example.xlsx
    9.6 KB · Views: 17
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 (?)
 
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.

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)
 
Last edited by a moderator:
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.
 
L-S represents all the numbers from L to S. I just missed one at level 25k in my original summary.
 
I just missed one at level 25k in my original summary.
If I understand you well, please see if it can help you with the attached solution.
 

Attachments

  • sfaheather84-navic10044.xlsx
    10.9 KB · Views: 6
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))
 
Back
Top