We help IT Professionals succeed at work.

I need to update an excel formula

wrt1mea
wrt1mea asked
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.

Please see the attached example.

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

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.

Author

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

Author

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