SUMPRODUCT - Duplicate entries count only once

Bloozntooz

New Member
Joined
May 20, 2023
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
How about:

051923 Sum Spreadsheet.xlsm
AB
1Andrew1
2George1
3George1
4Simon0
5Simon1
6David1
7David0
8
94
Sheet2
Cell Formulas
RangeFormula
A9A9=SUMPRODUCT(1/COUNTIF(A1:A7,A1:A7))
 
Upvote 0
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.
 
Upvote 0
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
BCDE
2WA-1
3A Shift30071
4Ops Field14Should be 11
5
WA1
Cell Formulas
RangeFormula
C4C4=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
BCDEF
230071
3ShiftNameDesignationLocation
48.A.0AndrewOps Field OperatorWA-1
58.A.0AndrewOps Field OperatorWA-11
67.A.0CraigOps Field OperatorWA-11
78.A.0StevenOps Field OperatorWA-11
88.A.0StevenOps Field OperatorWA-11
98.A.0ArthurOps Field OperatorWA-11
108.A.0ArthurOps Field OperatorWA-1
117.A.4BillOps Field OperatorWA-11
127.A.4BillOps Field OperatorWA-1
138.A.0SimonOps Field OperatorWA-11
148.A.0SimonOps Field OperatorWA-11
158.A.0HarryOps Field OperatorWA-1
168.A.0HarryOps Field OperatorWA-1
178.A.0LenOps Field OperatorWA-11
188.A.0MichaelOps Field OperatorWA-11
198.A.0MichaelOps Field OperatorWA-11
207.A.0AlmarOps Field OperatorWA-11
218.A.0ShaunOps Field OperatorWA-11
228.A.0ShaunOps Field OperatorWA-1
238.A.0FredOps Field OperatorWA-11
24
25Should 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.
 
Upvote 0
I'm seeing there are 12 (not 11) that meet the criteria? Yes/No? What am I missing?
 
Upvote 0
Valid counts are: Andrew, Craig, Steven, Arthur, Bill, Simon, Len, Michael, Almar, Shaun, Fred = 11
 
Upvote 0
On this forum I have found a formula that works correctly except it only picks up on the first name:

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.
 
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top