Running total of Occurence

[*shudder*]

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

 

[texasalynn]

make your formula

=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]

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]

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*]

make your formula<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 <o:p></o:p>

[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

 

[*shudder*]

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*]

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.

 

[jasonb75]

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*]

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]

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?