Array formula help please

[jstone4880]

This array formula:

=SUM(IF(child_name=$B3,1,0))

Returns the number of occurrences of the name in B3 as counted in the named range child_name.

However, I have copied the formula down so that users can just add the names and not worry about the formula - so I am getting a count of the blanks.

Could somebody add to this so that if B3 is blank it just leaves the cell blank and only counts if there is a name in B3?

This is for a safeguarding log so all help gratefully received!

 

[Mike LH]

Hi,

Your formula is too complicated for what you're trying to do, try this one which will return zero if B3 is blank


=COUNTIF(Child_Name,B3)


or to return a blank cell

=IF(B3="","",COUNTIF(Child_Name,B3))

 

[Latha]

you can use =sum(if(child_name=$B3,1,0)-COUNTBLANK($B3))

 

[jstone4880]

Worked like a charm Mike LH and Latha I must remember that COUNTBLANK for the future.

More help if possible:

=IFERROR(INDEX(child_name,MATCH(0,COUNTIF(B$2:B2,child_name),0)),"")

This formula returns the unique children so that all the duplicate occurrences are avoided on the summary page.

Can they then be put in alphabetical order? Is

 

[Mike LH]

ADVERTISEMENT

Hi,

This like your formula MUST go in B3. Array enter the formula then drag down until it returns blanks.


=IFERROR(INDEX($A$1:$A$20,MATCH(0,COUNTIF($A$1:$A$20,"<"&$A$1:$A$20)-SUM(COUNTIF($A$1:$A$20,$B$2:B2)),0)),"")

 

[jstone4880]

Was going to say - is this VBA territory?

 

[jstone4880]

ADVERTISEMENT

Oh thanks - will give that a go now.

 

[jstone4880]

This is just returning zeros for me. I have put into B3 and dragged down.

Some more context: on the first sheet all the occurrences of child protection concerns are recorded in date order - so some children will (unfortunately) have repeated occurrences. The names are a named range called child_name.

On the second sheet is an analysis of the children and how many times they appear on the sheet. A simple but powerful way to identify children who appear several times - something must be wrong and phone calls to social care etc. are prompted.

 

[Latha]

you can try the below :
add a column left to the column where the children name is present. and write the formula below "to find the sort order for that alphabetic in that names range"
=COUNTIF($B$2:$B$11,"<="&B2) (B2 to B11 is the range where I have some names in it)

after the above :
add another column to the right of the names and write this formula : =VLOOKUP(ROW()-ROW($C$1),$A$2:$B$11,2,FALSE) and drag it until you find a black cell.

Im unable to attach files. hence, tried explaining my best.

 

[Mike LH]

Hi,

If it' resyrning zeroes then it sounds like the formula is referencing the wrong range. The way I wrote the formula the data are in A1:A20 and the formula goes in B3 of the same worksheet. If you want it to work on a different sheet then we have to specify the worksheet name or use your named range like this.


=IFERROR(INDEX(Child_Name,MATCH(0,COUNTIF(Child_Name,"<"&Child_Name)-SUM(COUNTIF(Child_Name,$B$2:B2)),0)),"")