# Count data in column, ignore duplicates

Status
Not open for further replies.

#### EmmaFos

##### New Member
Hello out there!
Hoping someone may help, I am scheduling blocks of work by week and need a formula to divide C3 by count rows in sheet1 & insert into the respective date. The below formula works perfectly when summing all the numbers, the issue I have is when blocks double up in a date column, I need the duplicates to be ignored or counted across the dates.
IE, the sum of G3:G4 in Sheet1 is 24 across a 16 day block - it's the 16 days I need to C3 divided by and I'm not quite sure how to include that in my formula?

I would really appreciate the help!

Excel Formula:
``=IF(OR(Sheet1!G\$23=1,Sheet1!G\$24=1,Sheet1!G\$25=1,Sheet1!G\$26=1,Sheet1!G\$27=1),Sheet2!\$C\$5/SUM(Sheet1!\$G\$23:\$EB\$27),"")``

Schedule_Test.xlsx
ABCDEFGHIJKLMNOPQRST
2HoursNumbers6-Jun-2213-Jun-2220-Jun-2227-Jun-224-Jul-2211-Jul-2218-Jul-2225-Jul-221-Aug-228-Aug-2215-Aug-2222-Aug-2229-Aug-225-Sep-2212-Sep-2219-Sep-22
3BL013939.35123.10468755555555555555555
Sheet2
Cell Formulas
RangeFormula
B3B3=SUM(Sheet1!D3:D11)
C3C3=B3/32
E3:T3E3=IF(OR(Sheet1!G\$3=1,Sheet1!G\$4=1),Sheet2!\$C\$3/SUM(Sheet1!\$G\$3:\$W\$4),"")

Schedule_Test.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
2Durations DaysDurations WeeksHoursNumbers6-Jun-2213-Jun-2220-Jun-2227-Jun-224-Jul-2211-Jul-2218-Jul-2225-Jul-221-Aug-228-Aug-2215-Aug-2222-Aug-2229-Aug-225-Sep-2212-Sep-2219-Sep-2226-Sep-22
3BL014081016.0511111111
4BL0112024290.31111111111111111
Sheet1
Cell Formulas
RangeFormula
B3:B4B3=C3*5
C3:C4C3=SUM(G3:DS3)

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Fluff

##### MrExcel MVP, Moderator
Duplicate Count columns if they have data, except duplicates

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Status
Not open for further replies.

Replies
7
Views
129
Replies
4
Views
151
Replies
3
Views
123
Replies
11
Views
249
Replies
7
Views
727

1,141,864
Messages
5,709,066
Members
421,611
Latest member
Lisa W

### 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.

### Which adblocker are you using?

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

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