# Running total of Occurence

#### *shudder*

##### Active Member
I would like to be able to apply a unique number (running total) to a row if a column contains the letter A, something like this:

A 1
B 0 (or blank)
C 0 ""
A 2
C 0
A 3
B 0
C 0
A 4

This information is then pulled across to another sheet which I have already worked out the formula for, however I would like it to leave an empty display rather than an #N/A answer. The formula I am using is this:

=INDEX(Sheet1!C\$2:C\$119,MATCH(\$A2,Sheet1!\$Z\$2:\$Z\$119,0))

Any help or pointers in the right direction would be appreciated.

Many Thanks

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### texasalynn

##### Well-known Member
Code:
``=IF(ISNA(INDEX(Sheet1!C\$2:C\$119,MATCH(\$A2,Sheet1!\$Z\$2:\$Z\$119,0))),"",INDEX(Sheet1!C\$2:C\$119,MATCH(\$A2,Sheet1!\$Z\$2:\$Z\$119,0)))``

#### WaterGypsy

##### Well-known Member
If the A's you are counting are in column A then this should do the trick :

=IF(A1="A",COUNTIFS(A\$1:A1,"A"),"")

(then copy the cell down the length of the column)

#### jasonb75

##### Well-known Member
I'm assuming that Sheet1!\$Z\$2:\$Z\$119 holds the running total, try

=IF(MAX(Sheet1!\$Z\$2:\$Z\$119),INDEX(Sheet1!C\$2:C\$119,MATCH(\$A2,Sheet1!\$Z\$2:\$Z\$119,0)),"")

For the running total I would use WaterGypsy's suggestion, but with COUNTIF rather than COUNTIFS.

#### *shudder*

##### Active Member
make your formula<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Code:
`` <o:p></o:p>``
Code:
``[FONT=Verdana][SIZE=2][COLOR=black][COLOR=black][FONT=Verdana]=IF(ISNA(INDEX(Sheet1!C\$2:C\$119,MATCH(\$A2,Sheet1!\$Z\$2:\$Z\$119,0))),"",INDEX(Sheet1!C\$2:C\$119,MATCH(\$A2,Sheet1!\$Z\$2:\$Z\$119,0)))<o:p></o:p>[/FONT][/COLOR][/COLOR][/SIZE][/FONT]``
<o></o>

This just pulls across zero's

Last edited:

#### *shudder*

##### Active Member
If the A's you are counting are in column A then this should do the trick :

=IF(A1="A",COUNTIFS(A\$1:A1,"A"),"")

(then copy the cell down the length of the column)

Thanks WaterGypsy, that works

#### *shudder*

##### Active Member
I'm assuming that Sheet1!\$Z\$2:\$Z\$119 holds the running total, try

=IF(MAX(Sheet1!\$Z\$2:\$Z\$119),INDEX(Sheet1!C\$2:C\$119,MATCH(\$A2,Sheet1!\$Z\$2:\$Z\$119,0)),"")

For the running total I would use WaterGypsy's suggestion, but with COUNTIF rather than COUNTIFS.

Thanks Jason but that still gives an #N/A answer?

I spotted the error on WaterGypsy's post and corrected, cheers for pointing it out.

Last edited:

#### jasonb75

##### Well-known Member
Maybe

=IF(MAX(Sheet1!\$Z\$2:\$Z\$119),INDEX(Sheet1!C\$2:C\$119,MATCH(MAX(Sheet1!\$Z\$2:\$Z\$119),Sheet1!\$Z\$2:\$Z\$119,0)),"")

#### *shudder*

##### Active Member
Maybe

=IF(MAX(Sheet1!\$Z\$2:\$Z\$119),INDEX(Sheet1!C\$2:C\$119,MATCH(MAX(Sheet1!\$Z\$2:\$Z\$119),Sheet1!\$Z\$2:\$Z\$119,0)),"")

Thanks Jason, however that simply pulls acrooss the same data.

I see you are using a MAX element in the formula, however I want to pull across unique data from the row i.e one row might contain the number 1 and I want it to pull across the corresponding info from column C, another row will contain a 2 and I want to pull across this info......

#### jasonb75

##### Well-known Member
Can you give us more detail on your data layout, at the moment we're having to guess what the ranges in your formula refer to.

i.e. where is the runing total located on the sheet, and where is the range of data being counted?

What do sheet1!Z2:Z119 and sheet1!C2:C119 refer to?

Whats in \$A2?

Replies
5
Views
342
Replies
2
Views
138
Replies
8
Views
375
Replies
2
Views
90
Replies
5
Views
177

1,190,590
Messages
5,981,813
Members
439,735
Latest member
Cbegg

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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