[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

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
0
Cartillo
Asked:
Cartillo
  • 4
  • 2
  • 2
2 Solutions
 
Raheman M. AbdulCommented:
Seems missing attachment.
0
 
CartilloAuthor Commented:
Hi,

Accidentally sent without attached the doc. Sorry.  
0
 
barry houdiniCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Raheman M. AbdulCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now