1. Counting Function (XL2K SP3)

I want to use a function to count items in one column based on values in another. For example... here is some sample data...

Solution Manning, Jean
Implementation Manning, Jean
Root Cause Manning, Jean
Implementation Lawson, Toni R.
Root Cause Starr, William
Root Cause Starr, William
Root Cause Johnston, Marcus

So I want to know how many items "Manning, Jean" has in Root cause, Solution, and Implementation. I was thinking of COUNTIF but not sure how to put it together so that the criteria all works. Countif only takes one argument and am not quite sure how to nest them (if that is how it has to be done) to get it to work.

Thanx so much for any assistance that can be rendered.

Dennis

<img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>

2. Re: Counting Function (XL2K SP3)

Countif/Sumif work with only 1 criteria. You could create a column to combine all the criteria into 1 and then use this as the criteria column for the countif if desired

An alternate solution is to use array formulas as described by Chip Pearson.

Steve

3. Re: Counting Function (XL2K SP3)

If that data is in cells A1:B:100, then the following formula will count what you asked:

<pre>=SUMPRODUCT(--(A2:A101="Root Cause"),--(B2:B101="Manning, Jean"))
</pre>

4. Re: Counting Function (XL2K SP3)

Dennis,

Attached is a one sheet workbook giving various counting examples. These samples originated from John Walkenbach.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•