Solved How to count items that are in a category in Excel

September 2, 2015 at 17:26:21
Specs: Windows 7
How can I count items if they are in a category in a separate set of items?

In a table, Column A contains the regions, Column B contains the countries in each region.

In another sheet I have a list of countries which are scheduled in different months.

I need to know how many times a country is scheduled for a month in each region, meaning I need a formula which can lookup if a country is in a region and count it if it is.

I would like to use the region-country table to count how many times a country in a region occurs.

Thank you for any response.


See More: How to count items that are in a category in Excel

Report •


✔ Best Answer
September 3, 2015 at 13:19:28
I'm not sure if this is what you are looking for, but you could use Named Ranges for your regions and then refer to the individual cells in the range. For example, if you named B47:B49 as Region1, then this formula should give you the total number of instances of all of the values in B47:B49 found in E2:E26:

EDIT:

=SUM(COUNTIF($E2:$E26,
 INDEX(Region1,1):INDEX(Region1,MATCH("*",Region1,-1))))

This is an Array formula and must be enter with Ctrl-Shift-Enter every time you edit it.

If you want to put a Region's name in a cell, such as A1, you could then refer to that Named Range with the INDIRECT function.

e.g. if you put Region1 in A1, this formula would be equivalent to the one above.

=SUM(COUNTIF($E2:$E26,
 INDEX(INDIRECT(A1),1):INDEX(INDIRECT(A1),MATCH("*",INDIRECT(A1),-1))))

This, of course is also an array formula.

BTW..If you are using SUM, you don't need the Addition Operator within the parenthesis, you can just use a comma. If you want to use the Addition Operator then you don't need the SUM function. It is redundant.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
September 2, 2015 at 18:07:42
What your looking for is probably the =COUNTIFS() function.

If you could post a small example of your spread sheet
with a before & after it would help.

Before posting, read this How-To, it explains the use of the < PRE > tags
when posting data.
http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

#2
September 3, 2015 at 09:03:21
Thanks!

Here is a link to a Google Sheet sample of what I'm looking for:

Sample

I need a formula for column C which will look at the tables located at A46:B148 and count how many times a country in a region occurs in E2:H35

message edited by Ransom12


Report •

#3
September 3, 2015 at 09:07:33
This was my attempt, but I would like a better code which would read changes if a region were to be updates.

=sum(countif(E2:E36,B47)+countif(E2:E36,B48)+countif(E2:E36,B49))


Report •

Related Solutions

#4
September 3, 2015 at 09:36:23
I can't access your "Sample" while at work, so maybe that's why I'm confused by your statement:

"I would like a better code which would read changes if a region were to be updates."

Why wouldn't your formula "read changes" if regions were updated? As long as your sheet is set to Automatic Calculation the formula should reflect any changes immediately.

What am I missing?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#5
September 3, 2015 at 10:20:22
With the code I am currently using, I have to refer to each individual country rather than having it look at range of countries in a table. So if the regions were to change, I would have to change all of the coding rather than just updating the table.

In the code

=sum(countif(E2:E36,B47)+countif(E2:E36,B48)+countif(E2:E36,B49))

E2:E26 is the range which contains all of the countries listed by month, and B47, B48, and B49 are all country names.

What I would like the code to do is countif E2:E36 contains any countries in a region. Instead of B47, it would count any countries in B47:B49.


Report •

#6
September 3, 2015 at 11:42:19
I guess I will need to see your spreadsheet, which I can do later.

You say that E2:E26 "contains all countries" and B47:B49 "are all country names" so I don't quite understand where the "region" enters into this.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
September 3, 2015 at 12:26:37
B47:B49 are all countries in one region.

I have all of the countries listed by month, but I need to know how many each region shows up, not just each country.


Report •

#8
September 3, 2015 at 13:19:28
✔ Best Answer
I'm not sure if this is what you are looking for, but you could use Named Ranges for your regions and then refer to the individual cells in the range. For example, if you named B47:B49 as Region1, then this formula should give you the total number of instances of all of the values in B47:B49 found in E2:E26:

EDIT:

=SUM(COUNTIF($E2:$E26,
 INDEX(Region1,1):INDEX(Region1,MATCH("*",Region1,-1))))

This is an Array formula and must be enter with Ctrl-Shift-Enter every time you edit it.

If you want to put a Region's name in a cell, such as A1, you could then refer to that Named Range with the INDIRECT function.

e.g. if you put Region1 in A1, this formula would be equivalent to the one above.

=SUM(COUNTIF($E2:$E26,
 INDEX(INDIRECT(A1),1):INDEX(INDIRECT(A1),MATCH("*",INDIRECT(A1),-1))))

This, of course is also an array formula.

BTW..If you are using SUM, you don't need the Addition Operator within the parenthesis, you can just use a comma. If you want to use the Addition Operator then you don't need the SUM function. It is redundant.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
September 3, 2015 at 14:21:38
Thank you very much!!

Report •


Ask Question