# COUNTA Function

#### Gos-C

##### Active Member
Hi everyone,

The following formula is in cell A4, copied down to cell A8:

=IF(D4<>"",MID(YEAR(D4),4,1)&TEXT(MONTH(D4),"00")&MID(C4,1,1)&"-"&MID(B4,1,1)&MID(B4,FIND(" ",B4)+1,1)&TEXT(COUNTA(\$D\$4:D4),"000"),"")

The following names are in range B4:B8:

Jane Doe
Sister Rock
David ****
Sussie Jay
Tom Jones

The following two-digit codes are in range C4:C8:

AB
XY
JT
PQ
EF

Cell D5 contains the date:
04-Aug-07

Cell D7 contains the date:
21-Aug-07

As a result, cell A5 shows 708X-SR001 and cell A7 shows 708P-SJ002.

If I enter 23-Aug-07 in cell D4, cell A4 would show 708A-JD001, and cells A5 and A7 would change to 708X-SR002 and 708P-SJ003, respectively, but I need cell A4 to show 708A-JD003 and cells A5 and A7 to retain their original values.

Thank you,
Gos-C

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Scott Huish

##### MrExcel MVP
First there's a couple of things with your formula that aren't necessary, and some things that can shorten it.

=IF(D4,RIGHT(YEAR(D4))&TEXT(D4,"mm")&LEFT(C4)&"-"&LEFT(B4)&MID(B4,FIND(" ",B4)+1,1)&TEXT(COUNT(\$D\$4:D4),"000"),"")

TEXT(MONTH(D4),"00") for example will return the same value as TEXT(D4,"mm")
You may also want to look at the LEFT and RIGHT functions. In that formula it looks like you are basing your last number on how many dates are in D4:D8, as such COUNT would work.

However, it will take VBA code to change it from the formula to the value once a value in D4:D8 has been changed. What do you want to have happen if a value in D4:D8 is deleted? Put the formula back?

#### Gos-C

##### Active Member
Hi HOTPEPPER,

Thanks for the tips - I always thought the first part of my IF statement must have =, <>, > or <.

The LEFT and RIGHT funtions are correct, and the three-digit number at the end is based on the number of dates when the date for that record is entered. In other words, when the first date is entered, the last three digits should be 001. When the next date is entered, the last three digits should be 002, etc. Therefore, I need to replace the COUNT function part of the formula in order to achieve that.

Hope this explains it more clearly.

Thanks,
Gos-C

#### Scott Huish

##### MrExcel MVP
It will take VBA to lock the values. Again, what do you want to do if a date is deleted in Column D? And what is the actual range you are using, or do you really only have 5 cells that you are doing this with?

#### Gos-C

##### Active Member

Hi HOTPEPPER,

Yes, if the date is deleted, put back the formula. The actual range is A4:AA1505.

Thank you,
Gos-C

#### Gos-C

##### Active Member
Hi everyone,

I still need help with this.

Any help?

Gos-C

#### Gos-C

##### Active Member
Any help?

Gos-c

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,626
Messages
5,838,429
Members
430,547
Latest member
jopshio

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