# EXCEL COUNTIF Functions

Registered User regular
edited June 2012
I have an =COUNTIF function.

=COUNTIF(A1:A100,"Warcraft") tells me how many rows have a column entry in A that says Warcraft.

=COUNTIF(B1:B100,"Male") tells me how many rows have a column entry in B that says male.

I want to determine how many rows have a column entry in A that says Warcraft AND a Column B that says Male. I.E. I want to figure out how many male Warcraft entries I have.

Bonus question: Column A can say Warcraft, Starcraft or Dune. I want to know how many entries say either Warcraft or Starcraft in column A and Male in Column B. I.E. I want to know how many male Blizzard gamers I have.

Can anyone help with these functions?

What is this I don't even.
Darkewolfe on

## Posts

• (\/)┌¶─¶┐(\/) pinch pinchRegistered User regular
Darkewolfe wrote:
I have an =COUNTIF function.

=COUNTIF(A1:A100,"Warcraft") tells me how many rows have a column entry in A that says Warcraft.

=COUNTIF(B1:B100,"Male") tells me how many rows have a column entry in B that says male.

I want to determine how many rows have a column entry in A that says Warcraft AND a Column B that says Male. I.E. I want to figure out how many male Warcraft entries I have.

Bonus question: Column A can say Warcraft, Starcraft or Dune. I want to know how many entries say either Warcraft or Starcraft in column A and Male in Column B. I.E. I want to know how many male Blizzard gamers I have.

Can anyone help with these functions?

Easy, non-fancy way:

in cell c1 make

=if(and(or(A1="Warcraft", A1="Starcraft", A1="Dune"),B1="Male"),1,0)

so you have a "1" in that cell if the two cells to the left meet those conditions

drag that down

then just count (or rather, sum) the C column.

keep in mind that excel can only do 7 nested if statments (including ands and ors) and if you're doing a large amount of them (say, a thousand rows of 7 nested if statements) you need to turn auto calculate off or you'll be waiting for it to calculate every time you make a change to any cell.

• WisconsinRegistered User regular
edited June 2012
better way

=COUNTIFS(A1:A100,"Warcraft", B1:B100,"Male") will tell you how many meet both those conditions and I believe it's an unlimited amount of variables you can put in. At least, I haven't hit a limit with this function.

for the multiple ones, follow the if statement above. You can also do it with multiple countifs instead of if statements as well

Veevee on
You can also use CONCATENATE to combine A and B, so you'd have things like WarcraftMale, StarcraftMale, StarcraftFemale, etc.

Then use the COUNTIF function to see how many WarcraftMale you have, another to count how many StarcraftMale you have, etc.

Using CONCATENATE will allow you to not have to worry about how many nested IFs you use (I think the newer versions don't actually have that 7 nested IFs limit anymore, but if anyone needs to open in 2003 or earlier, it's just good practice to avoid going over 7).

• Registered User regular
Veevee wrote: »
better way

=COUNTIFS(A1:A100,"Warcraft", B1:B100,"Male") will tell you how many meet both those conditions and I believe it's an unlimited amount of variables you can put in. At least, I haven't hit a limit with this function.

for the multiple ones, follow the if statement above. You can also do it with multiple countifs instead of if statements as well

I tried this one and kept getting a value error.

What is this I don't even.
• Registered User regular
LaOs wrote: »
You can also use CONCATENATE to combine A and B, so you'd have things like WarcraftMale, StarcraftMale, StarcraftFemale, etc.

Then use the COUNTIF function to see how many WarcraftMale you have, another to count how many StarcraftMale you have, etc.

Using CONCATENATE will allow you to not have to worry about how many nested IFs you use (I think the newer versions don't actually have that 7 nested IFs limit anymore, but if anyone needs to open in 2003 or earlier, it's just good practice to avoid going over 7).

This seems like it would be good except that I have way, way more values to check than just the examples provided. HMM. I'd like to do the COUNTIFS or the first example. Gonna give the first solution provided a try.

What is this I don't even.
• on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
Maybe you should just create a pivot table?

• Registered User regular
I'm not so hot with Excel's advanced functions. How could I create a pivot table that allowed me to view that info that way?

What is this I don't even.
• on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
easily.

Go to insert
Pivot Table
Put Gender and Game in the Row Label Box
Put your row id in the Values Box (Just create a column filled with 1s)

• on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
If you have a unique identifer for your rows, you can use that instead of a dummy column of 1s, but you have to click on the "Sum of COLUMNNAME" once it's in the value box and change the value field properties to "Count"

You can use CONCATENATE and then just a new COUNTIF for every combination you would like to count. Concatenate doesn't care how many different things it's pulling together (and you can pull together multiple columns if you wanted as well).

Like, what more are you working with?

Also, the COUNTIFS Veevee provided should work.

• Fuck Yes. That is an orderly anal warehouse. Registered User regular
edited June 2012
Even if you run into difficulty getting a pivot table to work it is well worth any effort you put in. Pivot tables are great (and people who don't know how to use them will think you are a wizard).

jackal on
• Registered User regular
LaOs wrote: »
You can use CONCATENATE and then just a new COUNTIF for every combination you would like to count. Concatenate doesn't care how many different things it's pulling together (and you can pull together multiple columns if you wanted as well).

Like, what more are you working with?

Also, the COUNTIFS Veevee provided should work.

For the real version of "Game" type I have 6 types. For the real version of "Gender" type I have 4 types. I'm trying to find out how many rows have 2 of the 6 game types matched to 2 of the 4 gender types, then 1 of the 6 game types matched to the same to build a ratio.

I think I have enough to play with for now.

=SUMPRODUCT(--(G1:G93="Warcraft"),--(D1:D93="Male")) gave me a basic result. Then I just do that multiple times for each entry, then sum those.

What is this I don't even.
• Registered User regular
Pivot tables are THE SHIT.

They are pretty easy too, it's mostly drag and drop.

So do you only care about either of two (of six) Game types matching to either two (of four) Gender types, and the rest is just noise? Then you want to know how one other (of the six) Game types matches to either of the same two (of four) Gender types? Or do you want to know how just one of the two Game types first used match to the same Gender types?

Like, you've got Games: Warcraft, Starcraft, Dune, Quake, Civilization, and Risk.

And you've got Genders: Male, Female, TransMale, TransFemale

Do you only care how many Males play Dune and Quake and how many Females play Dune and Quake, and then out of those, how many Males play Quake VS total Males & Females playing Quake (or Quake and Dune)?

• Registered User regular
LaOs wrote: »
So do you only care about either of two (of six) Game types matching to either two (of four) Gender types, and the rest is just noise? Then you want to know how one other (of the six) Game types matches to either of the same two (of four) Gender types? Or do you want to know how just one of the two Game types first used match to the same Gender types?

Like, you've got Games: Warcraft, Starcraft, Dune, Quake, Civilization, and Risk.

And you've got Genders: Male, Female, TransMale, TransFemale

Do you only care how many Males play Dune and Quake and how many Females play Dune and Quake, and then out of those, how many Males play Quake VS total Males & Females playing Quake (or Quake and Dune)?

Close, yes. I want to know how many cismales and transmales play dune and quake, and then I want to know how many cisfemales play dune and quake, so that I can determine what percentage of only those groups falls into the two break downs. Everything else is noise for this particular evaluation.

What is this I don't even.
So, in C1 CONCATENATE(Game1,Gender1) (and then fill down to the last row with data) and then in other cells you count to count: COUNTIF(C1:C100,"dunecismale") then COUNTIF(C1:C100,"quakecismale") then COUNTIF(C1:C100,"dunetransmale") then COUNTIF(C1:C100,"quaketransmale") and then COUNTIF(C1:C100,"dunecisfemale") then COUNTIF(C1:C100,"quakecisfemale") and then you can sum or ratio those numbers all you want. (Replace C100 with whatever the last row is).

• Registered User regular
That would... definitely work, but it's not very efficient. Might as well just do

=SUMPRODUCT(--(G1:G93="Warcraft"),--(D1:D93="Male"))

and then follow the same extra procedure. I'm not generating additional noise columns that way at least.

What is this I don't even.