Counting by criteria

[Domroy]

[FONT=&quot]In column "A" I have a number called LOA.
[/FONT]
[FONT=&quot]In column "AS" I have claim numbers.
[/FONT]
[FONT=&quot]In column "AN" I have DOS From.
[/FONT]
[FONT=&quot]"AO" is DOS to.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]I need two formulas.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Formula 1: "Is there more than one claim number per LOA? So, I need it to look at the LOA number, see if there are duplicates (which there are), and when there is, check and see if the claim number is a duplicate and give me a yes or no. This formula is in "AT" - not that it matters.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Formula 2: "Is there more than one "DOS From - DOS To" instance for a claim?" So it's similar. It looks at all the matching claim numbers, and then checks columns AN and AO to see if that pairing is the same for each or not. Also to return a YES/NO. This formula is an "AU" - again, not that it matters.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Help! And thanks!
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Judi[/FONT]

 

[Roderick_E]

see below

In column "A" I have a number called LOA.

In column "AS" I have claim numbers.

In column "AN" I have DOS From.

"AO" is DOS to.



I need two formulas.



Formula 1: "Is there more than one claim number per LOA? So, I need it to look at the LOA number, see if there are duplicates (which there are), and when there is, check and see if the claim number is a duplicate and give me a yes or no. This formula is in "AT" - not that it matters.

=IF(COUNTIFS(A:A,A2)>1,IF(COUNTIFS(AS:AS,AS2)>1,"YES","NO"),"")

Formula 2: "Is there more than one "DOS From - DOS To" instance for a claim?" So it's similar. It looks at all the matching claim numbers, and then checks columns AN and AO to see if that pairing is the same for each or not. Also to return a YES/NO. This formula is an "AU" - again, not that it matters.



Help! And thanks!



Judi

 

[Domroy]

That may have worked! I need to test, but thank you!