# formula to assign number to other numbers in array

#### BSWJON

##### Board Regular
Right ladies and gents here is my question of the week!

I need to determine how many individual kiln charges have been carried out in a month. The kiln charge numbers take the form of a 5 digit code. This code is entered into column B whenever data attributed to that charge is entered into the database. Therefore working out how many charges have been done is not simply a matter of counting the number of cell values, as one charge may have data entered 3 times, another 6, another once, etc.

An example of the data:
 Kiln Charge kiln date out Month

<tbody>
</tbody>
 11035 9 18/03/2014 March 11014 4 14/03/2014 March 11035 9 18/03/2014 March 4 11035 9 18/03/2014 March 11014 4 14/03/2014 March 11035 9 18/03/2014 March 11011 9 13/03/2014 March

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

So I am looking for a formula that counts the number of charges rather than the number of times a single charge has had data entered.

Off the back of this, I would like to display a chart showing how many charges of each pre-set charge length have occurred. I thought of just doing a chart which counted the number of charges and plotted them against cycle time data but again this doesnt work as it in fact counts all of the charge entries as single charges rather than grouping like charges.

I am probably not making any sense but if someone understands what I would like I would really appreciate the help.

Cheers

Jon

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
=SUM(IF(FREQUENCY(A2:A9,A2:A9)>0,1))

I'll give that a crack, the charges are in column B so should it be B2:B9 etc etc?

Yep

thank you DKaur, while your formula works well for knowing how many charges there are total, it doesn't physically number each charge.

Upon closer inspection the Rank formula works for me and what I need to do

Last edited:
Jon

Would Excel's built-in Pivot Table feature produce what you want - as shown here in columns H:I?

Excel Workbook
BCDEFGHIJ
1Kiln Chargekilndate outMonthCount of date out
211035918/03/14MarchKiln ChargeTotal
311014414/03/14March110111
411035918/03/14March110142
54110354
611035918/03/14MarchGrand Total7
711014414/03/14March
811035918/03/14March
911011913/03/14March
10
PT

That certainly shows me how many charges I have which is useful, more meaningful perhaps than just having the total generated by the formula.

Replies
5
Views
511
Replies
3
Views
834
Replies
3
Views
418
Replies
0
Views
466
Replies
10
Views
1K

1,203,138
Messages
6,053,717
Members
444,681
Latest member

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