EXCEL COUNTIF Functions

Darkewolfe

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?

Gooey

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.

Veevee

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

LaOs

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

Darkewolfe

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.

Darkewolfe

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.

Deebaser

Maybe you should just create a pivot table?

Darkewolfe

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?

Deebaser

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)

Deebaser

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"

LaOs

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.

jackal

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

Darkewolfe

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.

Dr. Frenchenstein

Pivot tables are THE SHIT.

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

LaOs

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)?

Darkewolfe

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.

LaOs

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

Darkewolfe

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.

LaOs

I've never used SUMPRODUCT but if it works, it works. You end up with one column for the CONCATENATE and then however many cells for your counts. You can always hide the unnecessary columns and combine all the calculations/counts you need into one cell, if you really just need one number/ratio.

Either way, seems I don't have any additional information for a different solution here. Good luck.

SkyGheNe

I say just concatenate the two columns as LaOs suggested then setup an array that assigns IDs to the unique values that you produce with that concatenate, then use a vlookup to ID them, pull into pivot table and voila, use count rather than sum to deduce how many of each you have.