• 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.

Help! My head hurts (Simple formula)

Quinsjim

New Member
Hello - I've been an enthusiastic lurker on this site - which has solved a whole load of headaches for me in the past, but here's a probably simple one I can't crack.

I'm trying to run a countif formula to tell me how many items are coming due in the next week - which works well

=(SUM((COUNTIF(Log!K6:K1000,TODAY()-WEEKDAY(TODAY())+{1,2,3,4,5,6,7}))))

But for some reason I cannot think how to also filter this to exclude the items which are also marked "completed". i.e. I am only interested in things coming due which are still open.
I have a column (Log!I6:I1000 which holds the variables "Open" or "Complete")

What is the formula I can't think of to do this simple validation?

Please help solve my Monday morning headache!
 
Hi, Sorry - here's the file (no laughing at the back please!)
 

Attachments

  • My headache file.xlsx
    57.4 KB · Views: 7
Hi,

See the attached file, Yellow cells on the Cover and Summary sheet has formulas, if the result are OK, than increase the ranges in formula.

And nice name of file. :) You had passed your headache to us :D

Regards,
 

Attachments

  • My headache file.xlsx
    57.6 KB · Views: 3
You could also do this with a PivotTable, although I'd need to get clearer about your definition of what "Due this week" and "Due this month" mean.
 
Back
Top