2 Criteria Counting
 

In reading some of the other posts, I think this might be a Countif or
Sumproduct function but haven't been able to get the formulas to work...

Here is the outcome I am looking to achieve ..

I have about 300 rows of data, each row represents an project. Each project
has a Project Manager, and each project manager has multiple projects. Some
projects are open and some are closed.

In column, 'E' have have the name of the Project Manager (Harris, Jones,
Smith, etc). In column 'G' I have the status of the project (open, closed,
cancelled). All of this information is in a Worksheet titled "DATA"

I am trying to show the number of "Open" projects "Harris" has in a separate
Worksheet titled "SUMMARY". I am confident that I will need to enter the
Project Manager's Name in column "A" of the "Summary" sheet, but would like a
formula to count the rest from the "DATA" worksheet.

DATA
Harris Open
Harris Closed
Harris Open


SUMMARY
Name Open Closed Cancelled
Harris 2 1 0
Jones
Smith


Any help would be greatly appreciated as the boss is getting restless.

Paul

In B2 on Summaary enter, copy across then down:

=SUMPRODUCT(--(DATA!$A$2:$A$300=$A2),--(DATA!$B$2:$B$30=B$1))

where $A2 houses a manager name, B$1 Open, C$1 Closed, and D$1
Cancelled, the project status values

PGiessler wrote:
In reading some of the other posts, I think this might be a Countif or
Sumproduct function but haven't been able to get the formulas to work...

Here is the outcome I am looking to achieve ..

I have about 300 rows of data, each row represents an project. Each project
has a Project Manager, and each project manager has multiple projects. Some
projects are open and some are closed.

In column, 'E' have have the name of the Project Manager (Harris, Jones,
Smith, etc). In column 'G' I have the status of the project (open, closed,
cancelled). All of this information is in a Worksheet titled "DATA"

I am trying to show the number of "Open" projects "Harris" has in a separate
Worksheet titled "SUMMARY". I am confident that I will need to enter the
Project Manager's Name in column "A" of the "Summary" sheet, but would like a
formula to count the rest from the "DATA" worksheet.

DATA
Harris Open
Harris Closed
Harris Open


SUMMARY
Name Open Closed Cancelled
Harris 2 1 0
Jones
Smith


Any help would be greatly appreciated as the boss is getting restless.

Paul

Thanks, Aladin ... works like a charm!

"Aladin Akyurek" wrote:

In B2 on Summaary enter, copy across then down:

=SUMPRODUCT(--(DATA!$A$2:$A$300=$A2),--(DATA!$B$2:$B$30=B$1))

where $A2 houses a manager name, B$1 Open, C$1 Closed, and D$1
Cancelled, the project status values

PGiessler wrote:
In reading some of the other posts, I think this might be a Countif or
Sumproduct function but haven't been able to get the formulas to work...

Here is the outcome I am looking to achieve ..

I have about 300 rows of data, each row represents an project. Each project
has a Project Manager, and each project manager has multiple projects. Some
projects are open and some are closed.

In column, 'E' have have the name of the Project Manager (Harris, Jones,
Smith, etc). In column 'G' I have the status of the project (open, closed,
cancelled). All of this information is in a Worksheet titled "DATA"

I am trying to show the number of "Open" projects "Harris" has in a separate
Worksheet titled "SUMMARY". I am confident that I will need to enter the
Project Manager's Name in column "A" of the "Summary" sheet, but would like a
formula to count the rest from the "DATA" worksheet.

DATA
Harris Open
Harris Closed
Harris Open


SUMMARY
Name Open Closed Cancelled
Harris 2 1 0
Jones
Smith


Any help would be greatly appreciated as the boss is getting restless.

Paul