Countif Ranges clarification

[adeel1]

Hello All
as per subject

Normal Syntax: =COUNTIF (range, criteria)

I see many experts to use different ranges like,

=COUNTIF(A2:A2,A2:A2)
=COUNTIF(A2:A2,A2:A10)
=COUNTIF(A2:A10,B1:B1)
=COUNTIF(A2:A2,A1:A1)
if there is any other range like this please also include,
please explain each that what is purpose and need to use this type of ranges
with return & where to use in which type of data, i have lot of google but there is no luck,please

also posted here
https://www.excelforum.com/excel-fo...countif-ranges-clarification.html#post4991029

Adeel

 

[AliGW]

It would be much simpler if you explained what you are trying to do.

 

[adeel1]

right now i just want to expel my confusion regarding this.

 

[AliGW]

So have you tried each of them in an Excel file? I am honestly not sure what your confusion is. The purpose of each of these formulae is to count cells with a criterion. Unless you explain in a bit more detail what it is you don't get, I don't think anyone can really help you.

 

[Khalid NGO]

Hi Adeel,

Range refers to all the cells e.g A2:A10 from which you want to count
Criteria is the condition that defines which cells will be counted.

Normally we use single cell for criteria say B2, but in some cases we have to use the range in Criteria then we use A2:A10, this turn the normal formula into Array Formula which needs to be entered with Ctrl+Shift+Enter.

As AliGW said you need to try it yourself with some variations or you need to explain your confusion in a bit more detail manner.

Regards,

 

[bosco_yip]

Here's the testing result with example data and your formulas :



1] C1 =COUNTIF(A2:A2,A2:A2)
=COUNTIF("Linda","Linda")
=1

2] C2 =COUNTIF(A2:A2,A2:A10)
=COUNTIF("Linda",{"Linda";"Mindy";"Ashley";"April";"Jill";"Ashley";"Becky";"Robin";"Linda"})
={1;0;0;0;0;0;0;0;1}

3] C3 =COUNTIF(A2:A10,B1:B1)
=COUNTIF({"Linda";"Mindy";"Ashley";"April";"Jill";"Ashley";"Becky";"Robin";"Linda"},"Ashley")
=2

4] C4 =COUNTIF(A2:A2,A1:A1)
=COUNTIF("Linda","Sue")
=0

Regards
Bosco

 

[Peter Bartholomew]

Excel performs all calculations as 2D array calculations, though this does en-compass situations where the number of rows or columns (or both) might be 1. In this context, your formula
Normal Syntax: =COUNTIF (range, criteria)
should be interpreted as 'for each criterion, evaluate the entire range'. That would return an array of results of the same size as the criteria range.

The problem with that is that Excel can only render a single value within a formula cell so, without CSE, the calculation will attempt to pick out a single criterion from the criteria range to evaluate. It does this by implicit intersection of the formula row and the criterion range. If the intersection fails or there are multiple options returned one of the familiar errors (#N/A or #VALUE!") will result.

If the formula is entered as an array using CSE, this process of reducing the criterion range is omitted and the array of results is returned as it should be. I have attached an example showing a 2D array of criteria searching a 2D range of values for the fastest times (a use case that goes beyond the ones you showed).



The exciting thing is that next year these defaults will be changed and the array solution will be returned unless you restrict the criterion parameter to a single value by either clicking on a single cell (as usual) or by selecting a value from a 1D range using the new function 'SINGLE(criteria)'.