[Solved] Count from 2 worksheets and multiple criteria

[gdaniels]

HI. I’m looking for help to count rows based on multiple criteria from 2 worksheets. The following are sample worksheets to help illustrate:


Worksheet “Data”

Code_Name City State
Red1 San Jose CA
Blue3 Los Angeles CA
Red1 Glendale CA
Black3 Turlock CA
Blue3 Reno NV
White1 Sparks NV
Black3 Modesto CA


Worksheet “Names”

Code_Name Group Current
Red1 01 Yes
Blue3 02 Yes
Black3 01 No
Blue4 02 Yes
White1 03 Yes
Green2 03 No
Green3 02 No

I need to count in the “Data” worksheet the number of times code_names appear in the ‘Code_Name’ column of the “Data” worksheet that have a ‘Group’ of “01” from the ‘Group’ column of the “Names” worksheet. In this sample the count is 3.

The ‘Code_Name’ column of the “Data” worksheet will contain duplicates which are to be included in the count. The ‘Code_Name’ column of the “Names” worksheet will not contain duplicates, and the ‘Group’ column will contain duplicates.

The logic is something like “Count all records in the “Data” worksheet where the ‘Code_Name’ equals the ‘Code_Name’ in the “Names” worksheet that have a ‘Group’ of “01”.

The formula I am trying to write should function such that all I need to do is change the ‘Group’ number to get a count based on that number. I.E. If I use “02” instead of “01”, I would get a count from the “Data” worksheet” of all records where the ‘Code_Name”(s) are in ‘Group’ “02”. In this sample the count would be “2”.

Hope I made this clear, as I am very confused after searching for a solution. Thanks in advance for any help.

[rcm]

please send a sample workbook next time,

[sktneer]

Find the attached sheet to see if this is what you are trying to achieve.

[gdaniels]

Thanks to Sktneer. Solved the problem.

[sktneer]

You're welcome. Thanks for the feedback and rep.