How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Saturday

How To Use Countif or Countifs In Merged Cells In Google Sheets

Posted by   on

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 of 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 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 despatch of some material. A second column has the name of persons who are despatching the consignment.


I want to find how many consignments were despatched on a particular date.


Most importantly, I don’t want to unmerge the cells.


The sample data looks like as below.


Countif In Merged Cells

The following COUNTIF formula in cell G3 will return 1 when the total consignment despatched 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?


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, clikc Format > Number > Date.


Conditional Count Merged Range

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 are 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!


Resources

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!