+ Reply to Thread
Results 1 to 6 of 6

combining 2 formulas

  1. #1
    Registered User
    Join Date
    02-22-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2013
    Posts
    91

    combining 2 formulas

    Hi, I have a list of Brands and within those brands I have different labels. I am trying to work out how many different labels are within in each brand. The brands are in column A and the labels in column B. I concatenated column A and B into column C. I put the first formula in D2 which is =COUNTIF($C$2:C2,C2). The second formula is in E2 which is =SUMIFS($D$2:$D$15,$D$2:$D$15,"=1",$A$2:$A$15,A2). The formulas work but I was wondering can I combine the 2 formulas into 1 without having to concatenate. I have been trying but without success. Hope someone can help me.
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: combining 2 formulas

    Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.

    In F2
    Please Login or Register  to view this content.
    and copy down.

    Is this what you are trying to achieve?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: combining 2 formulas

    Try

    C2

    =COUNTIFS(A$2:A2,A2,B$2:B2,B2)

    D2

    =SUMIFS($C$2:$C$15,$C$2:$C$15,"=1",$A$2:$A$15,A2)
    Martin

  4. #4
    Registered User
    Join Date
    02-22-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: combining 2 formulas

    Thanks mrice but you still are using 2 formulas instead of 1.

  5. #5
    Registered User
    Join Date
    02-22-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2013
    Posts
    91

    Re: combining 2 formulas

    Thanks sktneer, that is exactly the result I wanted....will practice the formula..

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: combining 2 formulas

    You're welcome.
    You may also click on * (star) to Add Reputation if the solution provided helped you. This is another way to say thanks.

+ 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. [SOLVED] Combining IF formulas
    By nav505 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2013, 01:24 PM
  2. Too many IFs - combining few formulas into one - need help
    By vemix in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-24-2013, 01:42 PM
  3. Combining formulas
    By Cmorgan in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-26-2012, 04:01 PM
  4. Combining two formulas
    By mrggutz in forum Excel General
    Replies: 13
    Last Post: 09-24-2010, 12:47 PM
  5. Combining two formulas
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2006, 05:25 AM

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