COUNTIFS Assistance with Duplicate Values & Multiple Criteria

[brandon12]

https://drive.google.com/open?id=1eqtXSg4kU9vIpss9uc6VUs6mVnzVHHDmStruggling to figure out accurate formulas to return the results I'm looking for. I have attached a spreadsheet for review.

I want this data to provide three values in G4, H4, & I4:

1) The total # of DA's in column C... I'm using: =COUNTIF(C:C,"DA")

2) The total # of occurrences of exactly 1 Service Type "DA" and 1 Service Type "THER NOTE" for the same Client (summing all occurences into one cell).

3) The total # of occurrences of exactly 1 Service Type "DA" and 2 or more Service Type "THER NOTE's" for the same Client (summing all occurrences into one cell).

The result should be:

1) 5

2) 2

3) 2​

 

[brandon12]

That did the trick. Thank you so much for all your assistance!

The MATCH generates errors if you have empty rows. You can try this:

=SUM(IFERROR(MATCH($C$2:$C$4000,$C$2:$C$4000,0)=ROW($C$2:$C$4000)-ROW($C$2)+1,0)*(COUNTIFS($C$2:$C$4000,$C$2:$C$4000,$D$2:$D$4000,"DA")=1)*(COUNTIFS($C$2:$C$4000,$C$2:$C$4000,$D$2:$D$4000,"THER NOTE")[COLOR=#ff0000]=[/COLOR]1))

confirmed with Control+Shift+Enter. Change the last = to > for the other formula.

 

[Eric W]

Glad it works for you!

 

[brandon12]

I'm attempting to make a modification to a current worksheet. I have three formulas in this worksheet:

1) DA Only: =SUM(IFERROR(MATCH($C$2:$C$3999,$C$2:$C$3999,0)=ROW($C$2:$C$3999)-ROW($C$2)+1,0)*(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"DA")=1)*(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"THER NOTE")=0))

2) DA & 1 THER NOTE: =SUM(IFERROR(MATCH($C$2:$C$3999,$C$2:$C$3999,0)=ROW($C$2:$C$3999)-ROW($C$2)+1,0)*(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"DA")=1)*(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"THER NOTE")=1))

3) DA & 2+ THER NOTES: =SUM(IFERROR(MATCH($C$2:$C$3999,$C$2:$C$3999,0)=ROW($C$2:$C$3999)-ROW($C$2)+1,0)*(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"DA")=1)*(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"THER NOTE")>1))

I would like to return the ClientName (column C) for all DA Only. I have provided the following link for review and highlight the portion I'm attempting to accomplish in blue.

https://drive.google.com/open?id=1qWCW5xqeVGxqoJQuL18MzlH0N0BPjA7c

 

[Eric W]

Put this array formula in I9 and drag down:

=IFERROR(INDEX(C:C,SMALL(IF(COUNTIF(I$8:I8,$C$2:$C$3999)=0,IF(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"DA")=1,IF(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"THER NOTE")=0,ROW($C$2:$C$3999)))),1)),"")

Confirm with Control+Shift+Enter.

 

[brandon12]

Eric, once again you have saved the day. Works wonderfully. Thanks again for your assistance!

Put this array formula in I9 and drag down:

=IFERROR(INDEX(C:C,SMALL(IF(COUNTIF(I$8:I8,$C$2:$C$3999)=0,IF(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"DA")=1,IF(COUNTIFS($C$2:$C$3999,$C$2:$C$3999,$D$2:$D$3999,"THER NOTE")=0,ROW($C$2:$C$3999)))),1)),"")

Confirm with Control+Shift+Enter.

 

[Eric W]

You're welcome.