Forum Discussion
Formula help
Assuming a situation where you wish to count the number of records in a list that match "A"
= COUNTIFS( List, "A" )
you can include the records with a blank by adding
= COUNTIFS( List, "" )
You could also combine the two terms using an array
= SUM( COUNTIFS( List, {"A",""} )
Is this what you need?
Thanks for this but I can't make it work unfortunately, not sure if I'm just doing it wrong! It is counting all blanks, not just the blanks for the number of rows where I have data so it wouldn't be usable for what I need.
I have a list of pupil names and whether they are EAL, SEN, FSM, PPI etc and I need to be able to count how many pupils meet the criteria of being non-EAL, which could be "No" or blank, non-SEN, which could be "N" or blank and non-PPI which will be F. If a pupil meets all 3 criteria I need them counting only once. I also need to be able to count pupils who are non-EAL and non-SEN, again only counting the pupil once.
Regards,
Fiona
I hope I'm making sense, I don't know how else to describe what I need.
It might be worth posting a sample workbook (no actual pupil names) so that the requirement can be better understood. I prefer using Excel tables to hold the data with no blank rows and formula references that do not extend beyond the actual data. I appreciate there are other working practices though. To ignore blank records you will need a further criterion in the COUNTIFS that tests whether the student name/ID is present.
The OR condition, achieved by an array of evaluations and committed with Ctrl+Shift+Enter, is not a basic technique and needs to be used with care, especially if more than one criterion has multiple options.
A key to your acronyms might help too; I am familiar with SEN and have sorted EAL (not so common in the leafy suburbs) but I have still to find my way through the rest of the educational terms.
Hi Peter,
I've been thinking about this and I think the best way round it for me is to use the other information I have to get the data I need. Using an array of cells won't work as such because each time the report is run there may be a different number of rows.
What I have decided to do is use the number of pupils with EAL, SEN and PPI then minus it from the total to give me the data for those that don't have any.
Thanks for your help Peter.
Regards,
Fiona
