#### jstone4880

##### New Member
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!

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Mike LH

##### Well-known Member
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

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

#### jstone4880

##### New Member
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

##### Well-known Member
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

##### New Member
Was going to say - is this VBA territory?

#### jstone4880

##### New Member
Oh thanks - will give that a go now.

#### jstone4880

##### New Member
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

##### Board Regular
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.

Last edited:

#### Mike LH

##### Well-known Member
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)),"")

Replies
5
Views
145
Replies
3
Views
348
Replies
1
Views
202
Replies
1
Views
308
Replies
2
Views
249

1,195,644
Messages
6,010,893
Members
441,571
Latest member
stolenweasel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back