Solved

# Modify SUMPRODUCT  formula

Posted on 2011-04-27
270 Views
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
0
Question by:Cartillo

LVL 18

Expert Comment

Seems missing attachment.
0

Author Comment

Hi,

Accidentally sent without attached the doc. Sorry.
0

LVL 50

Accepted Solution

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

LVL 18

Assisted Solution

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 Comment

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 Comment

Hi barryhoudini,

Sorry, it works perfectly.
0

LVL 50

Expert Comment

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

regards, barry
0

Author Closing Comment

Hi,

Thanks for the help
0

## Featured Post

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …