Counting Based on Two Items

Dan Lofdahl

I am making a sheet to track the number of proposals we send out and how many turn into projects.

I want to be able to count them by which Project Manager did it. So I have a PM field for the Project Managers initials. I was using fields for each PM's Proposals. The formula I used was COUNTIF(PM:PM, "RM") for example and it seems to work OK.

The part I am having problems with is getting the number of ones for each PM that turn into projects.

What I am looking for is a count of the projects that a particular PM was successful getting.

I have a field for Project Number which only has a value if the proposal turned into a project.

How do I count the number of projects attributed to each PM?

Is there a cleaner way to do this?

 

Thanks,

Henry Lewellen

Try this out? I would have 2 values, Count if PM and Count if "successful", to replace Jimmy and 7

 

=COUNTIFS([Value 1]124:[Value 1]126, "Jimmy", [Value 2]124:[Value 2]126, 7)

 

EXAMPLE: Count the number of times the name 'Jimmy' and the number 7 appear on the same row

Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

 

Dan Lofdahl

The second value can be anything. It does not have a specific number.

J. Craig Williams

Replace the 7 in Henry's formula with this:

 

NOT(ISBLANK(@cell))

 

If the proposal is blank, it won't  be counted. Otherwise, it will.

 

Craig

Dan Lofdahl

Works fine now. Thanks.