Prev: I cannot highlight a whole row or column
Next: multiple range in SUM,IF
From: Bradly on 15 Dec 2009 22:37
I have a list that shows the worker ID and the result of each work item. The
number of work items differs for each worker ID--the results of each item is
either "A" or "D". This is an example of a portion of the list:

Worker Status
008Q A
008Q D
008Q A
098Q D
098Q A
098Q D

I am trying to set up a new list that counts for each worker ID the total
number of work items with status "A". How can I go about doing this?

Thanks. Let me know if you need more information on this.

From: T. Valko on 15 Dec 2009 23:06
Assume your data is in the range A2:B7.

List the unique IDs in a range of cells:

D2 = 008Q
D3 = 098Q

Enter this formula in E2 and copy down as needed:

=SUMPRODUCT(--(A$2:A$7=D2),--(B$2:B$7="A"))

--
Biff
Microsoft Excel MVP


"Bradly" <Bradly(a)discussions.microsoft.com> wrote in message
news:11D1E3C3-A0C6-42A1-8E8D-66AE49AB686E(a)microsoft.com...
>I have a list that shows the worker ID and the result of each work item.
>The
> number of work items differs for each worker ID--the results of each item
> is
> either "A" or "D". This is an example of a portion of the list:
>
> Worker Status
> 008Q A
> 008Q D
> 008Q A
> 098Q D
> 098Q A
> 098Q D
>
> I am trying to set up a new list that counts for each worker ID the total
> number of work items with status "A". How can I go about doing this?
>
> Thanks. Let me know if you need more information on this.
>


From: Ashish Mathur on 19 Dec 2009 07:10
Hi,

Create a pivot table. Drag worker to the row area, status to the column
area and column area (again) to the data area.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Bradly" <Bradly(a)discussions.microsoft.com> wrote in message
news:11D1E3C3-A0C6-42A1-8E8D-66AE49AB686E(a)microsoft.com...
> I have a list that shows the worker ID and the result of each work item.
> The
> number of work items differs for each worker ID--the results of each item
> is
> either "A" or "D". This is an example of a portion of the list:
>
> Worker Status
> 008Q A
> 008Q D
> 008Q A
> 098Q D
> 098Q A
> 098Q D
>
> I am trying to set up a new list that counts for each worker ID the total
> number of work items with status "A". How can I go about doing this?
>
> Thanks. Let me know if you need more information on this.
>
From: Bernd P on 19 Dec 2009 09:14
Hello Bradly,

I would use a Pivot table.

If you do not like that you can use my UDF Pstat, for example:
http://sulprobil.com/html/pstat.html

Regards,
Bernd
 | 
Pages: 1
Prev: I cannot highlight a whole row or column
Next: multiple range in SUM,IF