COUNTIF with two variables

  • Hi All


    I am in need of some urgent help!


    I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells


    What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4


    I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")



    I will upload the spreadsheet when I get home as it will not allow me to do so at school


    Many Thanks

  • Re: COUNTIF with two variables


    if you have Excel 2007 or later, then try:


    =COUNTIFS(Q5yr7, "0- 1Hour", Q12yr7,4)


    else try:


    =SUMPRODUCT(--(Q5yr7="0- 1Hour"),--(Q12yr7=4))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: COUNTIF with two variables


    edit: oops, someone posted already, guess I should've refreshed first :P
    something along the lines of this help?


    =SUMPRODUCT((A1:A6="a")*(B1:B6=4))

  • Re: COUNTIF with two variables




    Hi there


    Thanks for the fast reply, when I enter that formula in I get the #VALUE! display - its really annoying me this as I have probably made such a silly mistake which is buggering it up and I need it urgently for coursework!


    I have attached the spreadsheet to this reply - if yourself or any other member wouldn't mind having a look and seeing if they could find a solution I would be EXTREMELY grateful!!

  • Re: COUNTIF with two variables


    It is because your 2 ranges are different sizes:


    Q5yr7 covers E1:E11, while Q12yr7 covers L2:L11. They should both start at row 1 or both start at row 2...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: COUNTIF with two variables


    If you make the cells in column A of the Analysis sheet exactly match column E of the results sheet, then you can use one formula in B6, like:


    =COUNTIFS(INDIRECT("Q5"&"yr"&SUBSTITUTE(B$5,"Year ","")), $A6, INDIRECT("Q12"&"yr"&SUBSTITUTE(B$5,"Year ","")),4)


    and copy down and across the table...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: COUNTIF with two variables


    Quote from NBVC;616844

    If you make the cells in column A of the Analysis sheet exactly match column E of the results sheet, then you can use one formula in B6, like:


    =COUNTIFS(INDIRECT("Q5"&"yr"&SUBSTITUTE(B$5,"Year ","")), $A6, INDIRECT("Q12"&"yr"&SUBSTITUTE(B$5,"Year ","")),4)


    and copy down and across the table...


    Thank you so much!


    You are a legend! :)

  • Re: COUNTIF with two variables


    Quote from JackMarian;616875

    Thank you so much!


    You are a legend! :)


    Spoke to early! Another problem


    I have completed my tables for years 7 to 9 using the formula =SUMPRODUCT(--(Q5yr7="0- 1Hour"),--(Q12yr7=4)) changing where appropriate.

    I am now at the stage of producing the year 10 - 12 tables. The target grade (last part of the formula) changes to a letter e.g. =SUMPRODUCT(--(Q5yr12="0- 1Hour"),--(Q12yr10=A*)


    Whenever I enter this formula, or any other ones it comes up saying the formula is containing any error. This has only started happening with the introduction of the letter target grades - again I am probably being stupid again but all help is gratefully accepted!

  • Re: COUNTIF with two variables


    Does the A* need to be in quotes:


    =SUMPRODUCT(--(Q5yr12="0- 1Hour"),--(Q12yr10="A*"))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: COUNTIF with two variables


    Quote from NBVC;616883

    Does the A* need to be in quotes:


    =SUMPRODUCT(--(Q5yr12="0- 1Hour"),--(Q12yr10="A*"))


    Right all sorted!! Once again thank you very much for your help mate!

  • Re: COUNTIF with two variables


    You're welcome.. have a good evening.


    Remember when using text strings in formulas, you wrap them in quotes, numbers don't get wrapped in quotes ;)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!