Home/ Questions/Q 8926
Next
In Process
Computing Staff
  • 2
Asked: 2021-11-30T10:12:05+00:00 In:

How To Count Items That Are In A Category In Excel

  • 2

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.

Share

    1 Answer

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

      • 0