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

Open in new window


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

Open in new window


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?
ckelsoeAsked:
Who is Participating?
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.

Saurabh Singh TeotiaCommented:
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
0
ckelsoeAuthor 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))

Open in new window


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
0
Rory ArchibaldCommented:
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)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

ckelsoeAuthor 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.
0
Rory ArchibaldCommented:
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]))))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ckelsoeAuthor Commented:
Well - that is what I had for single date columns elsewhere. I think I confused myself by having to look at two date columns and tried to over complicate what I thought I needed. Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.