Help with - =if,and,or combined with countif

**galvinpaddy** · 06-23-2012, 10:00 AM

Good afternoon happy campers,

Worksheet =
Column A (range - A5:A70) - name
Column C (range C5:C70) - Date required
Column E (range E5:E70) - Date completed
Cell K2 - 01/01/2012
Cell L2 - 31/12/2012

In the above, lets say a team of people are scheduled to complete a task every week, the sheet contains dates from last year through to planned dates for next year.

Any weeks that pass without a check, a question mark (?) is placed into Column E relating to the expected date in Column C (to simply highlight "why is it not done")

Now i want my formula to calculate:
Out of all the info in Column C, only utilise 2012 (between K2 & L2 'start of year & end of year')
Calculate how many times the question mark (?) appears
AND
Out of this data, show me how many of the cells (in this year) contain a date.
I have added the formula

Please Login or Register  to view this content.

to show me if the check was completed on/after its dues date - works fine.
After seeing this work i became a little 'Excel' happy, and jumped into 'IF,OR,AND' to try and get the result as mentioned above.

Please Login or Register  to view this content.

But i only receive a #VALUE error, and am stumped on what im doing wrong.
I think it may have something to do with my COUNTIF part but im unsure, could someone please turn the light on for me lol

kind regards, galvinpaddy

**MarvinP** · 06-23-2012, 10:07 AM

Hey,

What if you replace all "?" with a non wild-card character and try again? See http://office.microsoft.com/en-us/ex...005203612.aspx

**galvinpaddy** · 06-23-2012, 10:09 AM

Sample file attached.

(formulas SLIGHTLY modified to match reference cells.)

In repsonse to MarvinP - i changed the "?" to "W" and changed a cell in Column E to W but same error.

**MarvinP** · 06-23-2012, 10:40 AM

Is this what you want it G2?

Please Login or Register  to view this content.

**galvinpaddy** · 06-23-2012, 10:52 AM

Seems to work :D
Many thanks, could you explain why changing it to E2<>"?" makes it work? is it to do with only calculating one cell at a time instead of the whole range??

Either way, thanks alot!

**MarvinP** · 06-23-2012, 11:12 AM

Hi,

I went to the Formula Tab and looked at your formula using the Evaluate Tool. It didn't like the (E5:E70,"?"). I tried this simple formula in a single cell and was given an error. It seemed to me you really wanted it on a single line so I changed it to E2<>"?" and it came back TRUE. It seemed to me that is what you really wanted anyhow.

It all came down to the Evaluate Tool and seeing what part was giving the problem. Then attacking that single part of your equation. I hope this trial and error method helps...