How to use COUNTIFS for merged celss

[Henry_paul_616]

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.

 

[pecoflyer]

Using Merged Cells Effectively in Excel - Excel Solutions

There's one simple piece of advice for using merged cells effectively in Excel...

excel.solutions

 

[bosco_yip]

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

 

[Peter Bartholomew]

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))),
  N(dates=calendar))

I suspect you are using a legacy version of Excel in which case I am not the person to help further.