Multiple IFs?

Hi Mark - If i was a forum guru I would be able to give you a single formula to put into Col C - maybe something like index sum mod columns blah blah! However, if you are willing to accept a helper column then this way should work. All of these formulea have been previously posted on this forum - i have just mixed and match to meet your needs.

Ok

In E1 confirmed with CSE and dragged down

=INDEX(Claim,MATCH(0,IF(MAX(NOT(COUNTIF($E$1:E1,Claim))*(COUNTIF(Claim,">"&Claim)+1))=(COUNTIF(Claim,">"&Claim)+1),0,1),0))

This will give you a unique count of all Claim #'s from col a. You can add new claim #'s to col a and they will appear in col e

But first you need to put this formula into the name manager

=OFFSET(Sheet1!$A$2,0,0,COUNT(IF(Sheet1!$A$2:$A$1000="","",1)),1)

then put

=COUNTIF(A:A,E2)

in F2 - it is a simple count if of the number of times a unique claim number appears in col a.

then put

=SUMIF(A:A,E2,B:B)/F2

in C2

and you have your answer.

See attached. I would reocommend that you rearrange the worksheet to make it nicer. ie hide the helper cols.

Any probs please ask.