Nested Count if

[Darin Kramer]

Hi Guys!

Question is :
Two columns of data
Column A is names (say a1:a10) and includes names Pete and Sam
Column B is date (say jan, feb, march)

I want to COUNT for all entries of Name 1, AND for a date requirement in
Column B. Think its a nested countif....

So in above Example - Sams name may appear four times in column A, and
next to first instance says Jan, second says March, third says Jan and
fourth says Jan... I want a formulae that counts the 4 appearences of
JAN for Sam.... help....

Regareds

D

 

[Guest]

Hi
=SUMPRODUCT(--(A1:A10="Sam"),--(B1:B10="Jan"))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

You can also use a simple array formula:

{=SUM((A1:A10=E1)*(B1:B10=E2)) }

where E1 is the name and E2 the date that you want to match in A1:A10 and
B1:B10 respectively.
How it works... eact element of teh 'A' array is either 0 or 1 if it matches
E1. Each element of the 'B' array is also 0 or 1 it there's a match with E2.
then the elements of A are multiplied by B so that where matches are found
the value is 1 else 0

Chip Pearson has an excecellent website with this kind of stuff
http://cpearson.com/excel/array.htm

HTH
Patrick Molloy
Microsoft Excel MVP