# Countifs Index Match

1. ## 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

2. ## 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.

3. ## 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. ## 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. ## Re: Countifs Index Match

duplicate post

6. ## 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,

7. ## 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. ## 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

##### Users Browsing this Thread

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

#### 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