count cells in excel with multiple criteria?

Question

i need a formula in excel that will count the number of entires with two sets of criteria ? i have a column with Company names and another with company rating eg: B1,B2,B3 etc...

I need a formula that will tell me the total number of entries if both the name and rating are met. i have tried a example below ?

Col A Column B
level
shannonc1
shannonc1
shannonc2
shannonc3
shannonc4
shannonc6
shannonc1
davidc1


c1
shannon"value need to show here the total amount of times that shannon shows C1"

Answer 1

Here's a shorter example (the IF statement is unneeded):

=SUM((A2:A9= "shannon")*(B2:B9= "c1"))

Since it is an array formula, you need to CTRL+Shift+Enter after typing it in.

Answer 2

HERE'S the FORMULA - but you have to remove the space between the asterix and the second open parenthesis or it won't work!

You would use the following formula to get a count:

=SUM(IF((A:A="shannon")* (B:B="C1"),1))

(copy and paste - remove the space between the asterix and the second open parenthesis)

(IMPORTANT: in order for this formula to work you have to hit CTRL+SHIFT+ENTER instead of just hitting enter after pasting the formula into the cell. This is how you activate a conditional formula)

-The A:A and B:B are simply stating that you want to look in the entire column for results. If want to make it a specific range, change to that - for example A1:A15 would be column A rows 1-15.

-The asterix is the code for "and" in conditional formulas.

-You can change "shannon" and "C1" to match what you want it to match. ("david" "C6", etc.)

-The "1" at the end of the formula tells it to add 1 for every time "Shannon" appears in column A and "C1" appears in column B. For the example you gave you would get the number "3" as the result.

Hope this helps!

Answer 3

If these cells of "data" are in one column, select the column and convert Text to Columns. In Excel 2007 it is done under the Data Tab, Data tools. Select a space as the delimeter and let Excel break the text up into columns. Then use a COUNTIF formula on each of the columns for each of the items that you want. Once you have the items separated into individual cells in columns you can use a formula like this: =COUNTIF(A:D,E1) this will count all the occurances of the contents of cell E1 that are in columns A, B, C, and D. If E1 has dog, it will count dog, if it has bird, it will count bird, etc.

Answer 4

I would use a pivot table for this and is probably best for all your data to be sorted.

From the top menu in excel, select Data, then PivotTable&PivotChartReport.
Click on the "next", this will take you to the next level of the wizard. If the area you want to pivot is selected, then "next" otherwise highlight all your data.(click and drag to do this)
Then select "new worksheet" and finish....this sets up your pivot to begin the report.
You should now see 2 new windows on the spreadsheet, one with "shannon" and "c1" in.
Drag "shannon" to the "drop Row fields here" space and drop.
Drag "c1" to the "drop column fields here" space and drop.
Finally drag another "c1" from the window to the "drop data items here" space and drop.

Hey presto...all your data is sorted and counted for you....Hope this helps

Answer 5

Stella Bing's answer seems right, but it didn't work for me. I did get it to work by using this formula:

=SUM(IF(A2:A9="shannon", IF(B2:B9="c1",1,0),0))
using the same control-shift-enter (that's command-shift-enter on a Mac) to create a conditional formula. It's strange, but I had to use specific cells. Using: =sum(if(A:A="shannon",... would give me an error: #NUM!

Answer 6

Something doesn't make sense in your additional information.
What is in col A and what is in Col B?

I assume you are familiar with the COUNTIF function?

Answer 7

=COUNTIF(range,"*c1*")

I think that's what you wanted but you aren't very clear