+ Reply to Thread
Results 1 to 3 of 3

SUM + Count Formula

Hybrid View

  1. 04-19-2020, 04:38 AM #1
    geliedee
    geliedee is offline
    Forum Contributor geliedee's Avatar
    Join Date
    10-14-2013
    Location
    Manila, philippines
    MS-Off Ver
    Excel 2007
    Posts
    399

    SUM + Count Formula

    Hi All,

    I have here a file which contains multiple customer numbers, agent commission and sum of total sales.

    One customers could have multiple products hence the duplication of products. now What I need to get the total sum of the Total Sales in USD. Since i have multiple customers, sum is treating this a different and and not unique customer.

    Total SUM should be : 75,800 but I am getting 345k if I use sum. I need to apply this to multiple workbooks hence I am thinking that formula is much better in this scenario.

    Hope someone can help me. Thanks alot and stay safe!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-19-2020, 04:49 AM #2
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,213

    Re: SUM + Count Formula

    Please try
    =SUMPRODUCT(--(MATCH(E2:E30,E2:E30,)=ROW(E2:E30)-ROW(E1)),E2:E30)
    sum for non duplicate total sales
    or

    =SUMPRODUCT(--(MATCH(A2:A30,A2:A30,)=ROW(E2:E30)-ROW(E1)),E2:E30)
    sum total sales for non duplicate customer
    Last edited by Bo_Ry; 04-19-2020 at 05:13 AM.
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  3. 04-19-2020, 05:04 AM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,747

    Re: SUM + Count Formula

    Use a Helper column, say, column F. In cell F2, put the formula:
    Formula: copy to clipboard 
    =IF(A2<>A1,1,0)
    and copy down.

    Then use the formula
    Formula: copy to clipboard 
    =SUMPRODUCT(E2:E30*F2:F30)


    I think you've missed off an entry of 800 in your calculation.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count Blank and count based on criteria only Formula Allowed
    By Harmender in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-21-2019, 12:26 PM
  2. Formula Required to Count Rows only if condition is meet or else dont count
    By Shahbazk in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-22-2017, 08:39 AM
  3. (Count Formula) Count if two seperate ranges create unique combination
    By Geekcrux in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2016, 07:00 AM
  4. Formula to count number of visible rows, and formula to count visible blanks
    By radoncadonc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-28-2015, 04:19 PM
  5. [SOLVED] Count formula not count hidden cells in table
    By tlacloche in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2013, 02:35 PM
  6. [SOLVED] Count and average formula needed to not count text field
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-07-2013, 05:30 PM
  7. Count formula for dates greater than 30 days old with exclusions and not count blanks
    By mmennealy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-29-2012, 05:25 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1