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
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!!

Files

• 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!