Conditional count

[cisbell_ddess]

I need to count unique occurances of names in column a which satisfy
conditions in two other columns. For instance:

A B C
Smith faculty annual fund
Smith faculty annual fund
Jones faculty annual fund
Smith faculty new gift
Jones faculty new gift

The number of unique occurances of Smith in column A where column B=faculty
and column C=annual fund. The answer of course is 2, but how do I write a
formula to answer that question in a long list of data?

 

[Don Guillett]

=sumproduct((a2:a22="smith")*(b2:b22="faculty")*(c2:c22="annual fund"))

 

[Ashish Mathur]

Hi,

Try this

1. Firstly assign headings to the three columns, say Name, Status and Fund
2. Now select the range including the header row and assign it a name
(Ctrl+F3), say try
3. Save the file on the desktop and click on any blank cell
4. Goto Data > Get External Data > From Other Sources > From Microsoft Query

Excel files

5. Select the Excel file on the desktop and click on Next
6. Select the named range (which appears on the left) and click on the
greater then symbol
7. Click on Next 3 times
8. In the last box, select View or edit data in MS Query
9. In the View menu, select Query properties > Unique records only > OK
10. Click on the SQL button and type the following after the from statement
(in the next line)

WHERE (try.Status='Faculty') AND (try.Fund='Annual fund')
)

10. type the following before the select statement (in the previous line)

Select count(*) from
(

This should get you the answer as 2
11. Go to File > Return Data to MS Office Excel
12. In the Import Data box, select table and the cell where you want the
answer

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com