Help! My head hurts (Simple formula)

[Quinsjim]

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!

 

[ashish mehra]

Consider posting a sample file.

Regards,
AM

 

[Quinsjim]

Hi, Sorry - here's the file (no laughing at the back please!)

 

[Somendra Misra]

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,

 

[Quinsjim]

Thank you - Hope the passed on headache evaporates as quickly as mine just has

 

[Somendra Misra]

Yes it did

You are welcome

Regards,

 

[jeffreyweir]

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.