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 (?)
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
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 (?)
My personal Web Excel Tutorials
Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,) you may need to use point (.) instead of
- 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.
- 100.000 (B, D, E, G, H)
- 80.000 (I, J, K)
- 50.000 (L-S)
-25.000 (F)
- 1 (C)
Last edited by p45cal; 2019-06-16 at 03:18 PM.
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.
My personal Web Excel Tutorials
Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,) you may need to use point (.) instead of
L-S represents all the numbers from L to S. I just missed one at level 25k in my original summary.
My personal Web Excel Tutorials
Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,) you may need to use point (.) instead of
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))
My personal Web Excel Tutorials
Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
Also for a decimal number I use a comma (,) you may need to use point (.) instead of
Bookmarks