With the help of LOOKUP, without unmerging, we can use COUNTIF or COUNTIFS in merged cells in Google Sheets. Here is how.
Since the count based on conditions (conditional count) is one of the most common problems to solve in Spreadsheets, you may find the below tips helpful.
Even if I don’t prefer merging cells, sometimes I also use them to present the values in a more reader-friendly way.
I suggest merging cells if it is necessary because it risks the Spreadsheet’s efficiency in manipulating data.
For example, when you merge A1:A10 and enter a value, just A1 contains that value, not all the cells in the merged range.
It may cause returning the wrong conditional count, and this post has the solution.
Let’s see how to use a Countif formula in a merged range in Google Sheets.
Countif in Merged Cells in Google Sheets
In the following example, I have a date column to record the dispatch of some material. A second column has the name of persons who are dispatching the consignment.
I want to find out how many consignments were dispatched on a particular date.
Most importantly, I don’t want to unmerge the cells.
The sample data looks like this:
The following COUNTIF formula in cell G3 will return 1 when the total consignment dispatched on that date is 4.
=countif( A3:A14, F3 )
Change the date (Countif criterion) in F3 to 17/08/2021. Again the result would be the same, not 5.
How to use Countif in merged cells in Google Sheets then?
The Formula for Conditional Count in Merged Cells
The following formula in cell G3 will do that magic!
=countif( ArrayFormula(lookup(row(A3:A14),if(len(A3:A14),row(A3:A14)),A3:A14)), F3 )
How?
In the above Countif, I have replaced the range A3:A14 with a LOOKUP array formula which virtually fills the dates in all the cells in the range.
Let’s take out that formula and enter it in cell E3. It will return some numbers (date values).
Select E3:E14, click Format > Number > Date.
Compare the range A3:A14 and E3:E14.
This way, we can use Countif in merged cells in Google Sheets.
Can you explain this formula?
Are you asking about the above LOOKUP array?
I have detailed that formula in another tutorial here – How to Use Sumif in Merged Cells in Google Sheets.
Countifs in Merged Cells in Google Sheets
The question of using Countifs arrives when we want to use more than one criterion in the conditional count.
Assume I want to find the number of consignments shipped by “Nicole” on 15/08/2021.
Here there are two criteria – “Nicole” and 15/08/2021. So we should use Countifs here.
Let’s see how to use Countifs with merged cells in Google Sheets.
There are two ranges to consider in the formula, and they are A3:A14 and B3:B14.
If the cells are not merged, we can use the below COUNTIFS.
=countifs( A3:A14, F3, B3:B14, G3 )
When using Countifs in merged cells, replace the ranges A3:A14 and B3:B14 with LOOKUP ranges as below.
=countifs( ARRAYFORMULA(lookup(row(A3:A14),if(len(A3:A14),row(A3:A14)),A3:A14)), F3, ARRAYFORMULA(lookup(row(B3:B14),if(len(B3:B14),row(B3:B14)),B3:B14)), G3 )
It will return 2, which is the number of consignments shipped by “Nicole” on 15/08/2021.
If you want to hardcode the criteria within COUNTIFS, replace F3 with date(2021,8,15) and G3 with “Nicole”
That’s all. Thanks for the stay. Enjoy!
