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)

Open in new window

Chart-Formula.xls
CartilloAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor 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
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Seems missing attachment.
0
 
CartilloAuthor Commented:
Hi,

Accidentally sent without attached the doc. Sorry.  
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Raheman M. AbdulConnect With a Mentor 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
 
CartilloAuthor 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
 
CartilloAuthor Commented:
Hi barryhoudini,

Sorry, it works perfectly.
0
 
barry houdiniCommented:
Which formula? If you try mine you'll see that D6 and D7 are different

regards, barry
0
 
CartilloAuthor 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.

All Courses

From novice to tech pro — start learning today.