# Excel Formula Help

I have a table named Assignments with two columns, Start and Finish. Start and finish are dates. I have created a dashboard sheet that will count the number of items that is occurring this week.

I have created the following formula, however, it returns an incorrect value (over counts the dates):

``````=SUMPRODUCT((Assignments[[Hardware Start]:[Hardware End]]>=Dashboard!\$C\$2+28)*(Assignments[[Hardware Start]:[Hardware End]]<=Dashboard!\$E\$2+28))
``````

Here is a sample of the data:
``````Start	End
8/10	8/29
8/10	8/29
8/3	        8/7
7/13	7/18
6/29	7/3
6/22	7/3
5/18	5/23
5/13	5/14
5/11	5/16
4/21	4/23
4/13	4/17
4/13	4/17
``````

If the criteria dates are 6/28 to 7/4 I would expect a result of 2. The forumula above returns a count of 3.

Thoughts on how to make this work accurately?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
I think your formula is giving the incorrect results further basis of data you have provided here is the logic why i say that..as the answer should be zero

so basically in the formula if you notice that you had 28 days to the start date and end date...

Now because of this your revised start date if you add 28 to 06/28+28 = 07/26

Similarly your end date will become= 07/04 +28 = 08/01

So now if you notice that their is no date in your data set which you provided to EE falls between those two dates..Now in order to resolve this can you update your sample file so that i can see what exactly you are trying to do here..and provide you a solution accordingly...

Now if you look at the data their are 3 values which are
Author Commented:
The correct formula for the data in the example is:

``````=SUMPRODUCT((Assignments[[Hardware Start]:[Hardware End]]>=Dashboard!\$C\$2+14)*(Assignments[[Hardware Start]:[Hardware End]]<=Dashboard!\$E\$2+14))
``````

Which puts the criteria dates equal to 6/28 - 7/4.

In the example data the following 2 items should match:
Start      End
8/10      8/29
8/10      8/29
8/3              8/7
7/13      7/18
6/29      7/3
6/22      7/3

5/18      5/23
5/13      5/14
5/11      5/16
4/21      4/23
4/13      4/17
4/13      4/17
Commented:
6/22 is not >= 6/28 so why would the second row match?

On the assumption that the end date will never be lower than the start date in your table, you should just need:
=SUMPRODUCT((Assignments[Hardware start]>=Dashboard!\$C\$2+14)*(Assignments[Hardware end]<=Dashboard!\$E\$2+14))

or more efficiently:
=COUNTIFS(Assignments[Hardware start],">="&Dashboard!\$C\$2+14,Assignments[Hardware end],"<="&Dashboard!\$E\$2+14)
Author Commented:
6/22 would be in the count as dates between 6/22 and 7/3 fall into the criteria range of 6/28 - 7/4. To be specific - if there is one day in the date range of Start and End (the start date in one column and the end date in the other column) that falls within the criteria date of C2 to E2 then it should be counted.

I am sorry I am not making myself clear enough in the description. Hopefully this helps.
Commented:
I see - that's not what your current formula does at all. :)

You need something like:

=SUMPRODUCT(--(((Dashboard!E2+14>=Assignments[Hardware start])*(Dashboard!C2+14<=Assignments[Hardware end]))))

Experts Exchange Solution brought to you by