Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested Count if
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested Count if
Hi
=SUMPRODUCT(--(A1:A10="Sam"),--(B1:B10="Jan")) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Darin Kramer" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested Count if
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 "Darin Kramer" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Nested count and vlookups | Excel Discussion (Misc queries) | |||
What is quicker? Nested or non nested ifs | Excel Programming |