2 Criteria Counting

P

PGiessler

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
 
A

Aladin Akyurek

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
 
P

PGiessler

Thanks, Aladin ... works like a charm!

Aladin Akyurek said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top