+ Reply to Thread
Results 1 to 4 of 4

Arrays in two colum and count the labels. Can the formula be simplified?

  1. #1
    Registered User
    Join Date
    02-05-2018
    Location
    Suzhou
    MS-Off Ver
    Office 10
    Posts
    28

    Arrays in two colum and count the labels. Can the formula be simplified?

    1.png
    I have simplified a bigger problem I have simplified the problem here. Your help would be greatly appreciated. What is most important to me is a simple shortest solution.

    I have to arrays:
    Array 1 - A1:A14 - These are the company names
    Array 2 - B1:B14 - These are the products that the company create.

    I have been trying to create a formula that says if A1:A14 is "Heinz" then look in the corresponding array if it says specified lables then count it. It this case I want it to count "Mummys Sauce", "Daddies Sauce", "Couzin Sauce"
    So in this simplified example the the answer would Count and equal the answer = 10
    Ialready have two solutions but they are not wuite correct

    ***DOES NOT WORK
    =SUMPRODUCT(--(A1:A14 ="Heinz"), --(B1:B14 = {"Mummys Sauce","Daddies Sauce","Couzin Sauce"}) )
    If I could get this style of formula working it but be EXACTLY what I need. The reason is because I reference external sheets on real solution the linkage because absolutely unmanageable. Im ideally looking for this to work.

    ***WORKS:
    =SUMPRODUCT(--(A1:A14 ="Heinz"), --((B1:B14 ="Mummys Sauce") + (B1:B14 ="Daddies Sauce") + (B1:B14 ="Couzin Sauce") >0) )
    This solution does work but Ive tried using it. But when I add all the linkage it becomes massive. Each cell 10 pages long. Hence me wondering if the first formula can be fixed

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Arrays in two colum and count the labels. Can the formula be simplified?

    Try this:

    =SUM(COUNTIFS(A1:A14,"Heinz",B1:B14,{"Mummys Sauce","Daddies Sauce","Couzin Sauce"}))

  3. #3
    Registered User
    Join Date
    02-05-2018
    Location
    Suzhou
    MS-Off Ver
    Office 10
    Posts
    28

    Re: Arrays in two colum and count the labels. Can the formula be simplified?

    This is a perfect solution. Thank you so much

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Arrays in two colum and count the labels. Can the formula be simplified?

    You’re welcome. Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Last edited by 63falcondude; 02-26-2018 at 09:01 AM. Reason: Rep Added

+ 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] Arrays in two colum and count the labels help? Stuck
    By Michael Dimond in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-06-2018, 05:16 PM
  2. Replies: 3
    Last Post: 10-23-2014, 04:50 PM
  3. What formula to count total student based on 4 different colum
    By redza in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2014, 12:04 AM
  4. [SOLVED] Count if colum B=20 and Colum A=ok
    By roofi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2014, 12:03 PM
  5. Formula Help matchin colum a with colum b to display colum c
    By dbe82 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2013, 10:11 PM
  6. [SOLVED] Colum count based ón values In colum a and b
    By 2001jesper in forum Excel General
    Replies: 16
    Last Post: 11-05-2012, 03:28 PM
  7. Can Formula Be Simplified?
    By natei6 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-31-2007, 04:56 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