+ Reply to Thread
Results 1 to 7 of 7

Help with IF & SUMIF Formula to Recognize Company Names and Total Up Values

  1. #1
    Registered User
    Join Date
    11-30-2018
    Location
    Airzona
    MS-Off Ver
    2010
    Posts
    5

    Help with IF & SUMIF Formula to Recognize Company Names and Total Up Values

    Hello,

    I originally got some help for my issue and came up with this formula:

    =IF(AB11=AB10,"",SUMIF(AB:AB,AB10,AC:AC))

    The reason for this is to have excel realize when Company1 ends and to total the value, then continue on for reach company. The actual sheet is over 20,000 rows.

    On the test sheet this formula works because I have company names listed as Company1, Company2, ect.. to keep names private. However, this formula no longer works when dealing with the actual company names on the real file.

    Is there anyway to adjust this, so it will work with real company names and return correct values? Test Sheet is attached, feel free to use random names if needed.
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Help with IF & SUMIF Formula to Recognize Company Names and Total Up Values

    how is it different
    In AC2 you have
    =IFERROR(IF(AB2=AB1, I2, AB1+AB2), 0)
    NOT
    =IF(AB11=AB10,"",SUMIF(AB:AB,AB10,AC:AC))
    your adding customer names AB1+AB2

    where is the formula you are not working with actual customer names

    customer names still seem to be customer1,2,etc
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with IF & SUMIF Formula to Recognize Company Names and Total Up Values

    With a pivot table.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    11-30-2018
    Location
    Airzona
    MS-Off Ver
    2010
    Posts
    5

    Re: Help with IF & SUMIF Formula to Recognize Company Names and Total Up Values

    Oeldere

    Thank you. This might be the easiest solution since the actual file is so massive. Are you able to explain how you setup the values for the pivot table? I am unfamiliar with Pivot Tables and can't seem to
    replicate what you did. I can't attach an image since I haven't posted enough


    etaf

    The AC Column Formula was just tabulating value information from another column to be more visible. The Formula in AD =IF(AB11=AB10,"",SUMIF(AB:AB,AB10,AC:AC)) is what I needed to get working.
    I kept Company names private as I can't release that information, but was hoping for any clarification on how to make work for the real company names.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with IF & SUMIF Formula to Recognize Company Names and Total Up Values

    I made a table of the data

    Insert => table.

    After that I made a pivot table.

    Insert => pivot table => new sheet => OK

    Then drag an drop the headers in the boxes.

    You have to try this a couple of times to see what information you get.

    I is easier if the table contains a few data and a few columns.

    Try this, and if you need help, just ask.

  6. #6
    Registered User
    Join Date
    11-30-2018
    Location
    Airzona
    MS-Off Ver
    2010
    Posts
    5

    Re: Help with IF & SUMIF Formula to Recognize Company Names and Total Up Values

    Awesome! Thank you. I got the most of what I need working. I think duration times got messed up a little bit, but we should be able to fix that ourselves. I will mark the question solved.

    Thanks again!

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with IF & SUMIF Formula to Recognize Company Names and Total Up Values

    Glad I could help.

    You can add reputation by clicking on the star * add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 02-07-2020, 03:37 PM
  2. Pivot pie chart work % of total of company and total profit
    By faodavid in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-30-2018, 11:51 AM
  3. [SOLVED] Get Company names based on Product names
    By rajeshn_in in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-27-2018, 12:53 PM
  4. How to extract company names with same formula?
    By london7871 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-23-2017, 01:16 AM
  5. [SOLVED] Count unique company names (Frequency Array Formula)
    By Carrol in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-02-2016, 11:18 AM
  6. Replies: 0
    Last Post: 04-04-2013, 04:52 PM
  7. Replies: 2
    Last Post: 05-20-2011, 12:44 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1