How to count two different values across two columns?

[seanpod]

In my spreadsheet, I have a long list of values, split by two different columns.

In column 1, there are names for departments and in column 2, there are rankings for each department.

I would like to be able to count where a non-numerical value appears in column 1 and corresponds to a specific numerical value in column 2.

So, if the department named "Business" in column 1 receives a rank of 1 in column 2, is there a way to count this occurrence?

[oeldere]

Probably it will, but can you add the result manualy in the file?

[seanpod]

Probably it will, but can you add the result manualy in the file?

I can count up the occurrences manually, but with hundreds of rows this is far too time consuming.

Is there a =countifs formula or something similar that I can use?

[oeldere]

Maybe I do understand the question.

3 solutions:

pivot table

count if

sumproduct

See the attached file.

[seanpod]

Maybe I do understand the question.

3 solutions:

pivot table

count if

sumproduct

See the attached file.

Thank you, the =sumproduct formula is what I am looking for.

Big question though, what if my second column (the "rank") had non-numerical data, such as a ranking written as "1st", "2nd", or "3rd" -- opposed to numerical values such as 1, 2, or 3?

I see that your formula uses an inequality to reference numbers greater than 0.

Thanks.

[oeldere]

All 3 solution do the same job, I would advice you to go for the pivottable solution.

It is a very strong tool, which will be expand in each version of excell.

With a pivot table you don't have to worry about the name-references
Business
Delivery
Mgmt
Production
Security
Shipping

The pivot table makes them for you.

With the other solutions you need to make the name references yourself, and you can forget one of them.