Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max value that meets a criteria | Excel Worksheet Functions | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
counting with a criteria in multiple transactions | Excel Worksheet Functions | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |