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​

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...h-duplicate-values-and-multiple-criteria.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Eric W]

H4:

=SUMPRODUCT(--(MATCH(B2:B13,B2:B13,0)=ROW(B2:B13)-ROW(B2)+1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"DA")=1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"THER NOTE")=1))

i4:

=SUMPRODUCT(--(MATCH(B2:B13,B2:B13,0)=ROW(B2:B13)-ROW(B2)+1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"DA")=1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"THER NOTE")>1))

 

[brandon12]

Eric,

I attempted to modify your formula to encompass more than the 13 rows. The following is my modification but I'm getting a #N/A error:

=SUMPRODUCT(--(MATCH(B2:B,B2:B,0)=ROW(B2:B)-ROW(B2)+1),--(COUNTIFS(B2:B,B2:B,C2:C,"DA")=1),--(COUNTIFS(B2:B,B2:B,C2:C,"THER NOTE")>1))

Any suggestions on how to alleviate this problem as I'm using this formula in many spreadsheets with 1,000 or more rows (none of which will be the same length). Is it a $ issue? Thanks!

H4:

=SUMPRODUCT(--(MATCH(B2:B13,B2:B13,0)=ROW(B2:B13)-ROW(B2)+1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"DA")=1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"THER NOTE")=1))

i4:

=SUMPRODUCT(--(MATCH(B2:B13,B2:B13,0)=ROW(B2:B13)-ROW(B2)+1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"DA")=1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"THER NOTE")>1))

 

[brandon12]

Correction: I meant to ask is it a #N/A error?

Eric,

I attempted to modify your formula to encompass more than the 13 rows. The following is my modification but I'm getting a #N/A error:

=SUMPRODUCT(--(MATCH(B2:B,B2:B,0)=ROW(B2:B)-ROW(B2)+1),--(COUNTIFS(B2:B,B2:B,C2:C,"DA")=1),--(COUNTIFS(B2:B,B2:B,C2:C,"THER NOTE")>1))

Any suggestions on how to alleviate this problem as I'm using this formula in many spreadsheets with 1,000 or more rows (none of which will be the same length). Is it a $ issue? Thanks!

 

[Eric W]

The problem is that you can't define a range like B2:B. You could just use a whole column reference, like B:B,

=SUMPRODUCT(--(MATCH(B:B,B:B,0)=ROW(B:B)),--(COUNTIFS(B:B,B:B,C:C,"DA")=1),--(COUNTIFS(B:B,B:B,C:C,"THER NOTE")>1))

but I'd highly discourage that. It will be very inefficient, and if you have a lot of them, it will slow down your sheet a lot. Safer to figure out the maximum row, say 4000, and use a range of B2:B4000. And depending on how many copies of the formula you have, and if you want to copy/drag them, you may need to add some $ signs.

 

[brandon12]

You were correct. Using the B:B column reference is extremely inefficient.

I attempted to plug in a range of B2:B4000 but that did not work as I received the #N/A error. I then attempted to inlude the $ as suggested but still received the same #N/A error. The following is the updated formula I used (I did insert an additional column in A which shifted my formula data one column right - B:B is now C:C):

=SUMPRODUCT(--(MATCH($C$2:$C$4000,$C$2:$C$4000,0)=ROW($C$2:$C$4000)-ROW(C2)+1),--(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")>1))

Thoughts?

The problem is that you can't define a range like B2:B. You could just use a whole column reference, like B:B,

=SUMPRODUCT(--(MATCH(B:B,B:B,0)=ROW(B:B)),--(COUNTIFS(B:B,B:B,C:C,"DA")=1),--(COUNTIFS(B:B,B:B,C:C,"THER NOTE")>1))

but I'd highly discourage that. It will be very inefficient, and if you have a lot of them, it will slow down your sheet a lot. Safer to figure out the maximum row, say 4000, and use a range of B2:B4000. And depending on how many copies of the formula you have, and if you want to copy/drag them, you may need to add some $ signs.

 

[Eric W]

The only thing I see offhand is that

-ROW(C2)+1

should be

-ROW($C$2)+1

but that shouldn't matter unless you copy and/or drag the formula, or add/delete rows. Other than than, I don't know what to say. If you could show a cut-down sample, it would help.

 

[brandon12]

I've posted a sample of the data below.

Formulas are as follows (Both of these formulas are returning the #N/A value):

DA + 1 Ther Note:

=SUMPRODUCT(--(MATCH($C$2:$C$4000,$C$2:$C$4000,0)=ROW($C$2:$C$4000)-ROW($C$2)+1),--(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")=1))

DA + 2 or more Ther Notes:

=SUMPRODUCT(--(MATCH($C$2:$C$4000,$C$2:$C$4000,0)=ROW($C$2:$C$4000)-ROW($C$2)+1),--(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")>1))

Sample Data Set:

[TABLE="width: 584"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Service ID[/TD]
[TD]Service Date[/TD]
[TD]ClientName[/TD]
[TD]Service Type[/TD]
[TD]CPT Code[/TD]
[TD]Location[/TD]
[TD]Staff Name[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]4/5/2018[/TD]
[TD]Test, Mia[/TD]
[TD]DA[/TD]
[TD]90791[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123457[/TD]
[TD]1/31/2018[/TD]
[TD]Test, Scott[/TD]
[TD]DA[/TD]
[TD]90791[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123458[/TD]
[TD]2/7/2018[/TD]
[TD]Test, Scott[/TD]
[TD]THER NOTE[/TD]
[TD]90834[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123459[/TD]
[TD]2/22/2018[/TD]
[TD]Test, Scott[/TD]
[TD]THER NOTE[/TD]
[TD]90834[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123460[/TD]
[TD]3/8/2018[/TD]
[TD]Test, Scott[/TD]
[TD]THER NOTE[/TD]
[TD]90834[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123461[/TD]
[TD]4/12/2018[/TD]
[TD]Test, Angie[/TD]
[TD]DA[/TD]
[TD]90791[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123462[/TD]
[TD]5/17/2018[/TD]
[TD]Test, Angie[/TD]
[TD]THER NOTE[/TD]
[TD]90834[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123463[/TD]
[TD]6/21/2018[/TD]
[TD]Test, John[/TD]
[TD]DA[/TD]
[TD]90791[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123464[/TD]
[TD]7/26/2018[/TD]
[TD]Test, John[/TD]
[TD]THER NOTE[/TD]
[TD]90834[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123465[/TD]
[TD]8/30/2018[/TD]
[TD]Test, Jane[/TD]
[TD]DA[/TD]
[TD]90791[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123466[/TD]
[TD]10/4/2018[/TD]
[TD]Test, Jane[/TD]
[TD]THER NOTE[/TD]
[TD]90834[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
[TR]
[TD]123467[/TD]
[TD]11/8/2018[/TD]
[TD]Test, Tom[/TD]
[TD]DA[/TD]
[TD]90791[/TD]
[TD]ABC[/TD]
[TD]Smith, Joe[/TD]
[/TR]
</tbody>[/TABLE]

The only thing I see offhand is that

-ROW(C2)+1

should be

-ROW($C$2)+1

but that shouldn't matter unless you copy and/or drag the formula, or add/delete rows. Other than than, I don't know what to say. If you could show a cut-down sample, it would help.

 

[Eric W]

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.