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

Please see the attached example.

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

=SUMPRODUCT(((MFDATA!$H$2:

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

=SUMPRODUCT( ( ((MFDATA!$H2:H5000>=A2)*(M

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.

=SUMPRODUCT(((MFDATA!$H2:H

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

regards, barry

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

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")+

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")+

....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")

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

regards, barry

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial