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/

EXCEL COUNTIF Functions

DarkewolfeDarkewolfe Registered User regular
edited June 2012 in Help / Advice Forum
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

  • GooeyGooey (\/)┌¶─¶┐(\/) 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.

    919UOwT.png
  • VeeveeVeevee 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
  • LaOsLaOs SaskatoonRegistered User regular
    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).

  • DarkewolfeDarkewolfe 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.
  • DarkewolfeDarkewolfe 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.
  • DeebaserDeebaser 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?

  • DarkewolfeDarkewolfe 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.
  • DeebaserDeebaser 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)

  • DeebaserDeebaser 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"

  • LaOsLaOs SaskatoonRegistered User regular
    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.

  • jackaljackal 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
  • DarkewolfeDarkewolfe 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.
  • Dr. FrenchensteinDr. Frenchenstein Registered User regular
    Pivot tables are THE SHIT.

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

  • LaOsLaOs SaskatoonRegistered User regular
    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)?

  • DarkewolfeDarkewolfe 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.
  • LaOsLaOs SaskatoonRegistered User regular
    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).

  • DarkewolfeDarkewolfe 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.
  • LaOsLaOs SaskatoonRegistered User regular
    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.

  • SkyGheNeSkyGheNe Registered User regular
    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.

Sign In or Register to comment.