Forum Discussion

Fiona Sawyers's avatar
Fiona Sawyers
Copper Contributor
Jun 10, 2019

Formula help

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

excel
Formulas and Functions

5 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 10, 2019

    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?

    • Fiona Sawyers's avatar
      Fiona Sawyers
      Copper Contributor
      Jun 10, 2019

      HiPeterBartholomew1 ,

       

      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.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Jun 10, 2019

        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.