Results 1 to 7 of 7

Thread: Formula to Counting Customer Sums

  1. #1
    Neophyte sfaheather84's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 MOS

    Formula to Counting Customer Sums



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    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

  3. #3
    Neophyte sfaheather84's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 MOS

    Question

    Quote Originally Posted by navic View Post
    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.

    Quote Originally Posted by navic View Post
    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 p45cal; 2019-06-16 at 02:18 PM.

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    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

  5. #5
    Neophyte sfaheather84's Avatar
    Join Date
    Jun 2019
    Posts
    3
    Articles
    0
    Excel Version
    Office 365 MOS
    L-S represents all the numbers from L to S. I just missed one at level 25k in my original summary.

  6. #6
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by sfaheather84 View Post
    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.
    Attached Files Attached Files
    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

  7. #7
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    885
    Articles
    0
    Excel Version
    Excel 2013
    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

Posting Permissions

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