Multiple IFs?

[markk2011]

I am trying to run a pricing report that will show profit for a years worth of claims. When I generate the report, it runs a generic count of what the cost to client for each claim and what was paid out to each party involved in the claim.

While the price for each party paid is displaying correctly from the raw report, the client cost is being duplicated. So if the cost to client was $1000.00 and two parties involved were paid out $200.00 each, the report show two lines for the claim, showing the correct amount for each party paid, but showing the client cost twice at $1000.00 rather than dividing it twice to display $500.00.

I need to come up with a formula that will allow the client cost to be divided and displayed up to 6 times (maximum party involvement). For entries that only involve two parties being paid out, I was able to use a simple IF OR statement to check the client cell before and after for duplicates (=IF(OR(A2=A1, A2=A3),P2/2,P2), but am having quite a conundrum coming up with a formula to check up to six entries.

Take Claim 2 for example, I would need the formula to recognize that there are six cells noting "Claim 2" and then divide the total cost by 6 to display that total in the new cell.

Claim 1 $1,500.00 $255.00
Claim 1 $1,500.00 $255.00
Claim 2 $5,550.00 $275.00
Claim 2 $5,550.00 $350.00
Claim 2 $5,550.00 $525.00
Claim 2 $5,550.00 $525.00
Claim 2 $5,550.00 $525.00
Claim 2 $5,550.00 $525.00
Claim 3 $3,650.00 $230.00
Claim 3 $3,650.00 $255.00
Claim 3 $3,650.00 $255.00
Claim 3 $3,650.00 $255.00
Claim 3 $3,650.00 $255.00
Claim 4 $725.00 $240.00
Claim 5 $725.00 $275.00
Claim 6 $1,600.00 $300.00
Claim 6 $1,600.00 $250.00

I have attached two sheets; the first that I figured out how to do this formula for up to two claims, and and also an example of what a list would look like with multiple claims.

Anyone that can help out would get all the kudos in the world from me!

[Blake 7]

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.