Count Same Number in three ranges

[Juan Pablo González]

Ok, are you ready for this ? you CAN use COUNTIF with multiple ranges, non contigous, etc.

For example, I have this three ranges:

A2:D2
{1,2,3,2}

given the name Rng1

B4:G5
{5,6,7,8,9,10;2,3,4,5,6,7}

name: Rng2

A7:A9
{2;4;6}

name: Rng3

Now, say I want to count the number of 2s in the three ranges. I can use this simple formula:

=SUM(COUNTIF(INDIRECT({"Rng1","Rng2","Rng3"}),2))

it's not even array entered !

Here's the Excel model (Yellow range = Rng1, Green = Rng2, Blue = Rng3)

you can also use this approach for non named ranges, i.e.:

=SUM(COUNTIF(INDIRECT({"A2:D2","B4:G5","A7:A9"}),2))

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows ME
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F10G10 =
	A	B	C	D	E	F	G
1	*	*	*	*	*	*	*
2	1	2	3	2	*	*	*
3	*	*	*	*	*	*	*
4	*	5	6	7	8	9	10
5	*	2	3	4	5	6	7
6	*	*	*	*	*	*	*
7	2	*	*	*	*	*	*
8	4	*	*	*	*	*	*
9	6	*	*	*	*	*	*
10	*	*	*	*	Answer	4	4
Sheet1 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.