• 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


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

How to use COUNTIFS for merged celss


New Member
Hi guys. I hope you all great. I need to count runs and they are confined by dates. The problem is that the dates cells are merged and if one date have more than one run, just the first one is counted.
How can i use COUNTIFS for merged cells?
Here is a print of a table without merged cells and with date merged cells and the excel sheet. I hope you can help me. Many thanks.



Excel Ninja
In respect of your layout,

The way to solve your problem please see follows:

1] The table in A15:L24 is copied from the table A27:L36 with no merged cells and with date in cells of D15:L15.

2] Just copied the merged cell format from D3:L3 to D15:L15. (by this way all merged cells filled with data)

Then, become >>

3] Formula is then given the right results, but with merged cells.

Please see attachment



Last edited:

Peter Bartholomew

Well-Known Member
1. You cannot use COUNTIF without a helper range because the function requires a range reference and not a simple array.
2. The result you show does not require any counting, it is simply a test of whether a column header matches the calendar column of dates.
3. If you are using 365, SCAN and LAMBDA can be used to fill in the missing dates from the header row.
= LET(
  dates, SCAN(0,dateHdr,LAMBDA(p,d,IF(d>0,d,p))),
I suspect you are using a legacy version of Excel in which case I am not the person to help further.