Hello out there!
Hoping someone may help, I am scheduling blocks of work by week and need a formula to divide C5 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 G23:G27 in Sheet1 is 36 across a 26 day block - it's the 26 days I need to C5 divided by and I'm not quite sure how to include that in my formula?
I would really appreciate the help!
Hoping someone may help, I am scheduling blocks of work by week and need a formula to divide C5 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 G23:G27 in Sheet1 is 36 across a 26 day block - it's the 26 days I need to C5 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 | |||
---|---|---|---|
Y | |||
25 | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5 | E5 | =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),"") |