# Modify SUMPRODUCT formula

Hi Experts,

I would like to request Experts help  modify the attached SUMPRODUCT  formula to allow count value only for the specified date from column B and C (Start and End Date) and prevent accumulation from preceding  week.  Attached as well the workbook for Experts perusal.

``````=IF(TODAY()<\$B2,"",100-(SUMPRODUCT(('Daily Tracking List'!\$B\$2:\$B\$196<=\$C2)*('Daily Tracking List'!\$D\$2:\$D\$196=D\$1)*('Daily Tracking List'!\$I\$2:\$I\$196="Yes")*'Daily Tracking List'!\$H\$2:\$H\$196)*1440)/(10080*ROWS(D\$2:D2))*100)
``````
Chart-Formula.xls
###### Who is Participating?

Commented:
Does this do it?

=IF(TODAY()<\$B2,"",100-(SUMPRODUCT(('Daily Tracking List'!\$B\$2:\$B\$196>=\$B2)*('Daily Tracking List'!\$B\$2:\$B\$196<=\$C2)*('Daily Tracking List'!\$D\$2:\$D\$196=D\$1)*('Daily Tracking List'!\$I\$2:\$I\$196="Yes")*'Daily Tracking List'!\$H\$2:\$H\$196)*1440)/(10080*ROWS(D\$2:D2))*100)

regards, barry
0

Senior Infrastructure Support Analyst & Systems DeveloperCommented:
Seems missing attachment.
0

Author Commented:
Hi,

Accidentally sent without attached the doc. Sorry.
0

Senior Infrastructure Support Analyst & Systems DeveloperCommented:
try this:

=IF(TODAY()<\$B2,"",100-(SUMPRODUCT(('Daily Tracking List'!\$B\$2:\$B\$196<=\$C2)*('Daily Tracking List'!\$D\$2:\$D\$196=D\$1)*('Daily Tracking List'!\$I\$2:\$I\$196="Yes")*'Daily Tracking List'!\$H\$2:\$H\$196)*1440)/(10080*ROWS(D2:D2))*100)

and copy this formula to all the cells
0

Author Commented:
Hi,

Thanks for the formula, have tested but not showing the right result. E.g. at column D "TH1", week-5 shows "99.9835" because at "daily tracking list" there was data with “Yes” at column I, however week-6 should not showing the same number since data at column I without “yes”, by right it should return value as “100.0000”.
0

Author Commented:
Hi barryhoudini,

Sorry, it works perfectly.
0

Commented:
Which formula? If you try mine you'll see that D6 and D7 are different

regards, barry
0

Author Commented:
Hi,

Thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.