Hi, This has been bugging me for a while - and hopefully you can help
I am counting the scores a particular set of children are getting in a test. I need to count how many of these children get between 4.5 and 5.5
I've been trying with the below expression - but it does not work! How do I express between 4.5 and 5.5?
COUNTIFS(Data::$B$3:$B$33,FALSE,Data::I$3:I33,">4.5,<5.5")÷COUNTIF(Data::$B$3:$B $33,FALSE)
MacBook Pro, Mac OS X (10.7.2)
Hi Gwyn,
Columns C to H are hidden to make a smaller screen shot.
Column B all cells are FALSE (unticked) to keep this simple.
The result appears in the Countifs table with this formula in A2:
=COUNTIFS(Data::B,"=FALSE",Data::I,">4.5",Data::I,"<5.5")
Note the Header Row in the Data table to allow reference to all Body Cells (for example, Data::B instead of a specific range of cells). That will allow you to add or delete rows, and the COUNTIFS formula will still work
The syntax for COUNTIFS is (from the Functions Panel)
COUNTIFS(test-values, condition, test-values…, condition…)
Regards,
Ian.
Hi Gwen,
I missed the bit about dividing the COUNTIFS result by a COUNTIF(FALSE) result:
COUNTIFS(Data::$B$3:$B$33,FALSE,Data::I$3:I33,">4.5,<5.5")÷COUNTIF(Data::$B$3:$B $33,FALSE)
Not sure what that is about 🙂.
Regards,
Ian.
Hi again Gwyn,
(Sorry for calling you Gwen).
I assume that Column B (TRUE?) is independent of Column I (Score)
I have added some Conditional Highlighting to show Scores that are between 4.5 and 5.5 (I hope I got this right).
Then added the second part of your formula
Oops, here is the full formula:
=COUNTIFS(Data::B,"=FALSE",Data::I,">4.5",Data::I,"<5.5")÷COUNTIF(Data::B,"=FALS E")
Two Scores meet >4.5 and <5.5
Divided by 4 FALSE values gives 0.5.
Regards,
Ian.
Message was edited by: Yellowbox. Oops, here is the full formula.
Update,
I can't get the formula to paste properly into my reply. Please ignore the space in FALS E. It should be FALSE
Ian.
Thank you so much Ian. More information and tutorial than I could ever have wished for. I appreciate this.
Could I ask you one follow up please - on the same subject? Your above formulae work perfectly. I have in the bottom a formuale for the whole class - but it throws a red triangle - not sure why.
COUNTIF(Data::Cymraeg,">4.5",Data::Cymraeg,"<5.5")÷31
It would help if you could click on the red triangle and report here what it says. I think the problem is that you need COUNTIFS not COUNTIF. You need the S when you have more than one column-condition pair.
SG
Hi Gwyn,
SG is right, you need COUNTIFS.
Also, if the formula is in the last row of the Data table, that row needs to be a Footer Row to prevent the formula refering to its own cell (because it refers to the whole of column I, not a range).
=COUNTIFS(I,">4.5",I,"<5.5")÷31
Row 1 is a Header (frozen), row 33 is a Footer. Therefore 31 Body Rows.
Regards,
Ian.
Again - thank you very much everyone - works a treat. Very much appreciated
Hi Gwyn,
Even better. Use the ROWS function to count the rows in the Data table. Subtract 2 (Header and Footer) to find the number of Body Rows (=number of children in that class). This assumes that every body row contains details for a child.
Formula in the Footer Row
=COUNTIFS(I,">4.5",I,"<5.5")÷(ROWS(I)−2)
Save as Template. Then for a new class, add or delete rows to suit. The formulas will still work without having to modify them.
Regards,
Ian.
Using COUNTIF with a range of numbers
