CountIF Array Formula

[GN0001]

Hello team,


I have a CountIF array formula

This is what I have in D4 to D9


1

3

1

2

2

1

This is what I have E4 to E9

3

1

2

2

5

1


This is my array function:


=COUNTIF($D$4:$D$9,$E$4:$E$9)


I am asking Excel to count the number of times the values in each cell in E4:E9 appears in the range D4:D9


The result comes as below, which is not correct:


1

1

1

1

1

1

what is the problem?

Thank you very much for the help,

GGGGG

 

[NARAYANK991]

Hi ,


What you should be doing is :


=COUNTIF($D$4:$D$9,E4)


Copy this down.


If you have to use your formula ( =COUNTIF($D$4:$D$9,$E$4:$E$9) ) , what you have to do is select a block of cells , say F4:F9 , type in your formula , and press CTRL SHIFT ENTER. You will get the correct answers.


Narayan

 

[sdsurzh]

Hi,


Adding to Excel Ninja Narayan formula you can also use COUNTIFS (if you have 2007 excel)


=COUNTIFS($D$4:$D$9,$E4)


Thanks,

Suresh Kumar S

 

[GN0001]

Thank you very much,

It worked,

I am glad,


Are you saying that for each Array function, should we select the cells first and then enter the formula into formula bar?


Can we copy and paste the function by dragging the corner of the cell containing the function?


GGGGG

 

[NARAYANK991]

Hi ,


There are two different types of formulae , both of which are referred to as array formulae.


In the first , the formula operates on arrays , but returns a single value ; consider a formula such as :


=SUM(A1:A5*B1:B5)


If this is entered as a normal formula , by using just the ENTER key , the formula will display a #VALUE! error , since the range A1:A5 cannot have one value.


However , if the above formula is entered as an array formula , by using CTRL SHIFT ENTER , you will get a valid number as the result ; what the formula will be calculating is :


A1*B1 + A2*B2 + A3*B3 + A4*B4 + A5*B5


The second type of array formula is the one where you select a range of cells , and then enter the formula , of course as an array formula , using CTRL SHIFT ENTER ; here the selected range is treated as one unit , and the formula operates on the entire range and not on individual cells within that range. If you try any normal operation on any single cell within the range , Excel will return an error message , and the operation will not succeed.


As an example , suppose you have a series of numbers in a range , say D4:D9 ; suppose you select another range , say G13:G18 ( the number of rows in this range has to be the same as the number of rows in the range D4:D9 ) , and enter the following array formula ( using CTRL SHIFT ENTER ) :


=SMALL(D4:D9,ROW(D4:D9)-ROW($D$4)+1)


What this will do is populate the range G13:G18 with the numbers from the range D4:D9 , in ascending order.


If you use the LARGE function instead of the SMALL function , the numbers will be sorted in descending order.


Narayan