+ Reply to Thread
Results 1 to 8 of 8

Countifs Index Match

  1. #1
    Registered User
    Join Date
    06-21-2014
    Location
    Sydney
    MS-Off Ver
    2011
    Posts
    92

    Countifs Index Match

    Hi All,

    I need to be able to count the number of Units that contribute 80% of activity, the data would look like attached

    Essentially the conditions are:
    Market Research
    Area
    Year.....(If you could work in a way to get another condition in for a year either 2013 or 2012 I would be forever grateful!)

    But I cannot seem to get a Countifs index Match to work here.

    Thanks so much in advance

    Keelin
    Attached Files Attached Files
    Last edited by Keelin; 07-16-2014 at 04:18 AM.

  2. #2
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Countifs Index Match

    I think you'll have to be a little more clear on what you are doing. Where do the 3, 3 and 4 come from? Doesn't seem that I can replicate those values based on your description of the question.
    Despite the high cost of living, it remains very popular.

    Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!

  3. #3
    Registered User
    Join Date
    06-21-2014
    Location
    Sydney
    MS-Off Ver
    2011
    Posts
    92

    Re: Countifs Index Match

    Its just a count of the units based on a number of criteria,

    e.g. if all of the activity adds up to 100% then how many units contribute to making up 80% or less of that, so thats where the 3, 3, and 4 come from.

    Its counting how many of them add up to 80 or less

  4. #4
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Countifs Index Match

    How is Area 3 4 and Area 2 3 then. Can you do a calculation example, say which ones you are counting and why?

    For the Market research for area 2 you could add 12,15,15 and get <80% for your 3 answer but then for area 3 you would also need to include the blank cell to get to 4. It just does not seem consistent based on what I am expecting.

  5. #5
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Countifs Index Match

    duplicate post

  6. #6
    Registered User
    Join Date
    06-21-2014
    Location
    Sydney
    MS-Off Ver
    2011
    Posts
    92

    Re: Countifs Index Match

    Hi,
    Its not a duplicate post from me at least! Maybe its a common problem this week!

    I am sooo sorry for not being clear enough I'm a bit stressed out today

    I read it back to myself and you're right - crystal clear as mud!!

    I have reworked the example in the spreadsheet

    I can achieve the first part:

    Sort highest to lowest per area
    Sum each one cumulatively in a helper column
    Stop when I reach 80 or less
    Flag each one that has been cumulatively summed to 80
    This will leave me with a list of flags for each area

    Part 2:
    Totally stuck!!
    I would like to count up the flags for each area
    I don't appear to be working in the conditions correctly

    I would like to use a COUNTIFS INDEX MATCH (as this would be the most flexible in terms of working in a 3rd condition (which I will need as I need to do a 2 year comparison 2012 - 2013)

    Thanks so much,
    Attached Files Attached Files
    Last edited by Keelin; 07-16-2014 at 08:35 AM.

  7. #7
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├•┤ Pew Pew
    Posts
    441

    Re: Countifs Index Match

    If you can do it with some helper columns off to the side and show the result with the calculations I am sure that we can condense it into a smaller set of formulas for you! When you have time just see if you can solve it with helper columns and post a workbook with a solution and I can give it another look.

    Right now for Area 3 I see 19,31,22 for Market Research. I am not sure what the 4th one is, which is why I am having issue determining a solution. Communicating over a forum isn't always the easiest!

  8. #8
    Registered User
    Join Date
    06-21-2014
    Location
    Sydney
    MS-Off Ver
    2011
    Posts
    92

    Re: Countifs Index Match

    Thanks Hawkeye,
    I'm getting the helper columns working now
    Just actually bringing back the flag count that I'm struggling with now

+ 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: 6
    Last Post: 04-30-2014, 02:42 AM
  2. [SOLVED] Index Match from a cell populated from index match
    By MarcLewis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-15-2013, 05:30 AM
  3. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  4. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  5. Replies: 5
    Last Post: 02-29-2012, 08:51 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