Count data in column, ignore duplicates

Status
Not open for further replies.

EmmaFos

New Member
Joined
Oct 25, 2020
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
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.
Status
Not open for further replies.

Forum statistics

Threads
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.
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
Top