As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Darkewolfe
Registered User regular

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?

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

0

## Posts

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.

=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

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

I tried this one and kept getting a value error.

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.

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)

Like, what more are you working with?

Also, the COUNTIFS Veevee provided should work.

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

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

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.

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

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