Formula help

[Fiona Sawyers]

Hi there,

I have a spreadsheet with various columns of data that I need to be able to count up but my countifs formula doesn't count the blanks so I need to be able to replace the blanks with F, for example but it needs to leave any value already entered.  I can then use the F in the countifs formula.  It has to be a formula so that it can then be copied down to all other rows of data using a macro.  This is so that I can ultimately count pupils who are not assigned in more than one category only once.

Any help would be much appreciated.

Thanks in advance,

Fiona

[#2]

@Fiona Sawyers 

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?

[#3]

Hi@Peter Bartholomew ,

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.

[#4]

@Fiona Sawyers 

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.

[#5]

@Peter Bartholomew 

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

[#6]

@Fiona Sawyers 

Can't fault the logic provided you ensure that you do not count multiple issues that impact the same pupil. 

A different numbers of rows is not necessarily a problem provided you use dynamic references that allow for such changes.  Tables and structured references probably offer the best solution but there are traditional techniques of counting and using OFFSET with the count that also work.