# Countifs formula counting the wrong column

1. ## Countifs formula counting the wrong column

Hello,

Here is my problem:
I have this Countifs formula =COUNTIFS(Milestones!F7:F63,FALSE,Milestones!I7:I63,"<"&TODAY())

Whereas, F has single cell linked checkboxes, and I is a column of "planned completion" dates. The dates are merged cells accounting for multiple rows/checkbox actions. (One milestone can have multiple actions, if any action is unchecked and the overall milestone date has passed, it should count the number of checkboxes as the number of past due.)

What I would like is for the formula to count if a checkbox is unchecked AND the planned completion date has already passed, thus giving me a number of actions that are past due. What is happening now, as far as I can tell, is that the formula is counting the number of dates that are past due, instead of the number of actions. Since the dates are merged cells, it is giving me the answer of 2 when there should be 7.

Is it possible using merged cells, and if not, is there a workaround you can think of??

Thank you!!!

2. ## Re: Countifs formula counting the wrong column

Provide a sample non-sensitive workbook...

Don't forget to click *

3. ## Re: Countifs formula counting the wrong column

The problem is the merged cells in column I
Only the top/left cell of a merged range actually contains a value.

If say I7:I10 is merged, and shows a date say 7/10/2014
Then only I7 actually contains the date, I8 9 and 10 are actually blank.

You would be best served to UNmerge those cells, and put the date in all cells in column I

4. ## Re: Countifs formula counting the wrong column

Thank you for the input. I tried unmerging and I am still having the same problem.

Mind taking a look? I highlighted the cell with the issue on the first tab.

5. ## Re: Countifs formula counting the wrong column

In addition to UNmerging the cells..
YOu have to put the date in ALL of the cells that were previously merged.

Looking at the table, it appears that I7:I11 was merged.
But when you unmerged it, the date was only in I9.
You need to put that date in ALL of those cells from I7 to I11

6. ## Re: Countifs formula counting the wrong column

Ah, of course. For formatting/looks/ease of use sake, would it be possible or logical to add an argument to the formula that instead of checking all i column it should check only i9,i14,i30, etc (whichever has the date)? And if so, what would that formula look like?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1