We help IT Professionals succeed at work.

# I need to update an excel formula

on
I need to update the sumproduct formulas that I am using to correctly display the number of times a date falls within a date range.

On the MFINFO tab, Column C, this is the formula I am using:

=SUMPRODUCT(((MFDATA!\$H\$2:\$H\$5000>=A2*(MFDATA!\$H\$2:\$H\$5000<=B2)+(MFDATA!\$I\$2:\$I\$5000>=A2*(MFDATA!\$I\$2:\$I\$5000<=B2)+(MFDATA!\$N\$2:\$N\$5000>=A2)*(MFDATA!\$N\$2:\$N\$5000<=B2)>0)*(MFDATA!\$M\$2:\$M\$5000="")*(MFDATA!\$K\$2:\$K\$5000="N")*(MFDATA!\$B\$2:\$B\$5000<>"")))). It looks through the MFDATA tab to see what dates fall where.

Sometimes the dates in column H, Column I and Column N do not fall in the same date range I am specifying. Is there a way to update the SUMPRODUCT formula to use OR so that if one of the dates in columns H,I, & N are within the date range its only going to count it once (of course providing the other criteria in the formula are met) ?

I think if I get that formula updated I can update the other ones.
12-7-11.xlsx
Comment
Watch Question

## View Solution Only

Most Valuable Expert 2012
Top Expert 2012

Commented:
I think your formula should be revised to:

=SUMPRODUCT( (  ((MFDATA!\$H2:H5000>=A2)*(MFDATA!\$H2:\$H5000<=B2)) + ((MFDATA!\$I2:\$I5000>=A2)*(MFDATA!\$I2:\$I5000<=B2)) + ((MFDATA!\$N2:\$N5000>=A2)*(MFDATA!\$N2:\$N5000<=B2)) )*(MFDATA!\$M2:\$M5000="")*(MFDATA!\$K2:\$K5000="N")*(MFDATA!\$B2:\$B5000<>"") )

The + in this case, serves as an OR - so H is tested for T/F, I is tested for T/F and N is tested for T/F - the + ensures that the result returns T/F for the set, then for that criteria, where M is blank, K is "N" and B is not blank, you should get results.

Please let me know how it works for you.

Commented:
dlmille...

Whend I try the solution you provided, it essentially doubles the total number for the count when I appply it to the production data, which I know is incorrect.

I tried on the test data as well (the example I provided) but it returns a number, 51, which is more that the total number of records, 30.

Thanks for helping!
Most Valuable Expert 2013
Commented:
Try this version, Ted

=SUMPRODUCT(((MFDATA!\$H2:H5000>=A2)*(MFDATA!\$H2:\$H5000<=B2)+(MFDATA!\$I2:\$I5000>=A2)*(MFDATA!\$I2:\$I5000<=B2)+(MFDATA!\$N2:\$N5000>=A2)*(MFDATA!\$N2:\$N5000<=B2)>0)*(MFDATA!\$M2:\$M5000="")*(MFDATA!\$K2:\$K5000="N")*(MFDATA!\$B2:\$B5000<>""))

That guarantees no double counting, even if all dates in a row meet the criteria

regards, barry

Commented:
You are the man! Thanks a ton!
Most Valuable Expert 2012
Top Expert 2012

Commented:
Sorry, I was in meetings till just now, or I would have responded earlier.

barry - thanks for the catch - looks like I needed to sustain the >0, though it didn't seem intuitive to me at the time.
Most Valuable Expert 2013

Commented:
Hello Dave,

I think the syntax of the original was off but, yes, >0 is required. Small example. If you wanted to count rows where col A = "x", "y" or "z" then you could use this setup using +

=SUMPRODUCT((A2:A100="x")+(A2:A100="y")+(A2:A100="z"))

No > 0 required because the conditions are mutually exclusive. if A2 is "x" it can't also be either "y" or "z" - each row can only be counted once.

But what if you wanted to count rows where there was at least one "x" in cols A, B or C.......then those conditions are not mutually exclusive so rather than this.....

=SUMPRODUCT((A2:A100="x")+(B2:B100="x)+(C2:C100="x"))

....which will count the total number of "x"s in the range A2:C100 (just an example - of course COUNTIF would be better for that), i.e. potentially counting each row up to 3 times......this version will count any row as 1 whether it contains 1, 2 or 3 "x"s

=SUMPRODUCT(((A2:A100="x")+(B2:B100="x)+(C2:C100="x")>0)+0)

I think that is analogous to the situation that wrt1mea is addressing

regards, barry