• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Which is best formula to achieve the result I want?

K

karenlb

New Member
Hi


I have a project actions and issues log. I have a range named "status" with dropdown lists for open, closed, development etc. I have a ranged named "actionopendate" with the dates the action started.


I have a dashboard and I want to count the number of actions that are open that started before a certain date. The date is found in cell ref C5.


The formula I have used is:

=SUMPRODUCT(--(STATUS="OPEN"),--(ACTIONOPENDATE<=C5))


Excel is returning #value!


I can't for the life of me work out where the error is. But it's there somewhere...


Anyone able to help me? Please?
 
Formula structure looks right...are the two ranges equal in size?

If using XL 2003 or earlier, you need to make sure the ranges don't call out entire columns.

Could there be an error in one of your cells (in the STATUS of ACTIONOPENDATE range)?
 
Luke M - you are my saviour!


The ranges were of unequal sizes - one cell out! doh!


All working perfectly and my dashboard looks - pretty!


Many thanks


K
 
You must log in or register to reply here.
Back
Top