SUMPRODUCT - Duplicate entries count only once

[Bloozntooz]

Office 2019 / Windows 10

I have searched numerous websites for a solution to this, but only found ones that partially do what I need. I must apologize for not being able to post a mini-spreadsheet due to limitations on my company PC.

I have a list of names in a column with a corresponding binary number in the next column which indicates if training has been completed.

The names may appear twice, and the 1's may appear once (but in either of the rows for that name), or twice (in both rows).

A very simplified representation is below.

As part of a much larger SUMPRODUCT formula, I am after a formula to count the values of duplicate names only once.

Names Training completed

Andrew 1

George 1

George 1

Simon 0

Simon 1

David 1

David 0

Total should be: 4

Thank you.

 

[kweaver]

How about:

051923 Sum Spreadsheet.xlsm
	A	B
1	Andrew	1
2	George	1
3	George	1
4	Simon	0
5	Simon	1
6	David	1
7	David	0
8
9	4
Sheet2

Cell Formulas
Range	Formula
A9	A9	=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))

 

[Bloozntooz]

Thank you for the reply.
I have tried that already but cannot get it to work - I really need to provide a mini spreadsheet for you to see the data, but I cannot right now. I will try to explain:

The formula I have included your suggestion into (in red) is:
=SUMPRODUCT(--(ISNUMBER(FIND("A",CustomView!$B$7:$B$700)))*--(ISNUMBER(FIND("Ops Field",CustomView!$F$7:$F$700)))*--(ISNUMBER(FIND("WA-1",CustomView!$G$7:$G$700)))*(1/COUNTIF(CustomView!$D$7:$D$700,CustomView!$D$7:$D$700)*CustomView!$AX$7:$AX$700))

The database tab is called "CustomView", which is is from rows 4:700, and contains the following:
Column B = work shifts (search criteria is 'A') [there is B,C,D as well]
Column D = Names list (the one I am the having problems with duplicates)
Column F = Work position (search criteria 'Ops Field') [there is Ops Snr Field, Supervisor etc as well]
Column G = Work areas (search criteria 'WA-1') [there is a mix of WA-2......WA-6 as well]
Column AX = The binary data (of passing assessments) with the 1's appearing in either of the 2 rows, or sometimes in both rows, of the associated duplicate names.

 

[Bloozntooz]

I can now paste the spreadsheet in to here.

This is the very simplified version of the calculation sheet, but represents the problem that I have:

Test Rev1.xlsx
	B	C	D	E
2	WA-1
3	A Shift	30071
4	Ops Field	14	Should be 11
5
WA1

Cell Formulas
Range	Formula
C4	C4	=SUMPRODUCT(--(ISNUMBER(FIND("A",CustomView!$B$4:$B$23)))*--(ISNUMBER(FIND("Ops Field",CustomView!$D$4:$D$23)))*--(ISNUMBER(FIND("WA-1",CustomView!$E$4:$E$23)))*CustomView!F4:F23)

This is the data tab:

Test Rev1.xlsx
	B	C	D	E	F
2					30071
3	Shift	Name	Designation	Location
4	8.A.0	Andrew	Ops Field Operator	WA-1
5	8.A.0	Andrew	Ops Field Operator	WA-1	1
6	7.A.0	Craig	Ops Field Operator	WA-1	1
7	8.A.0	Steven	Ops Field Operator	WA-1	1
8	8.A.0	Steven	Ops Field Operator	WA-1	1
9	8.A.0	Arthur	Ops Field Operator	WA-1	1
10	8.A.0	Arthur	Ops Field Operator	WA-1
11	7.A.4	Bill	Ops Field Operator	WA-1	1
12	7.A.4	Bill	Ops Field Operator	WA-1
13	8.A.0	Simon	Ops Field Operator	WA-1	1
14	8.A.0	Simon	Ops Field Operator	WA-1	1
15	8.A.0	Harry	Ops Field Operator	WA-1
16	8.A.0	Harry	Ops Field Operator	WA-1
17	8.A.0	Len	Ops Field Operator	WA-1	1
18	8.A.0	Michael	Ops Field Operator	WA-1	1
19	8.A.0	Michael	Ops Field Operator	WA-1	1
20	7.A.0	Almar	Ops Field Operator	WA-1	1
21	8.A.0	Shaun	Ops Field Operator	WA-1	1
22	8.A.0	Shaun	Ops Field Operator	WA-1
23	8.A.0	Fred	Ops Field Operator	WA-1	1
24
25				Should be:	11
26
CustomView

I do not want to count the binary entries in data tab row 'F' where there are duplicate names.
The '1's' can be in either upper or lower row of the duplicate name, or in both rows.

 

[kweaver]

I'm seeing there are 12 (not 11) that meet the criteria? Yes/No? What am I missing?

 

[Bloozntooz]

Valid counts are: Andrew, Craig, Steven, Arthur, Bill, Simon, Len, Michael, Almar, Shaun, Fred = 11

 

[Bloozntooz]

On this forum I have found a formula that works correctly except it only picks up on the first name:

If duplicate numbers exist then sum values in another column / same row

Hi Everybody! Although it was easy to figure out how to find duplicate values in a column and to calculate the number of times each entry has been duplicated, if at all, my need extends beyond that and this is where I now need your assistance, please. The scenario: If a number is duplicated...

www.mrexcel.com

The stand alone formula is:
=IF(AND(COUNTIF(C4:C23,C4)=COUNTIF(C4:C23,C4),COUNTIF(C4:C23,C4)>1),SUMIF(C4:C23,C4,F4:F23),"")

It only counts 'Andrew' once if the value '1' appears in column 'F' in either first or second row of the name, or in both rows.

I need this style to apply to the full range on the data list, and to incorporate it into the existing SUMPRODUCT formula stated earlier.

 

[Bloozntooz]

And apologies for misstating a sentence in an earlier post:
I do not want to count the binary entries in data tab row 'F' where there are duplicate names.
The '1's' can be in either upper or lower row of the duplicate name, or in both rows.

I meant to say:
I do not want to count the binary entries in data tab row 'F' twice where there are duplicate names.
The '1's' can be in either upper or lower row of the duplicate name, or in both rows.

 

[Bloozntooz]

Amendment to my post #7:
The stand alone formula does work for values in either the first or the second row of the duplicate name, but it adds them to give 2 if a '1' is in both.

Still struggling....

 

[Bloozntooz]

Update: after fiddling with the formula in post #7, I got it to work:
=IF(AND(COUNTIF(C4:C23,C4)=COUNTIF(C4:C23,C4),COUNTIF(C4:C23,C4)>1),IF(SUMIF(C4:C23,C4,F4:F23)>1,1,SUMIF(C4:C23,C4,F4:F23)))

But how do I incorporate this into the SUMPRODUCT formula in post #4?