Counting values which meet 2 criteria in Excel

[jonschofield]

I've been wrestling with this problem for a while now so any help would be appreciated.

I have a spreadsheet showing jobs that are assigned to different people and what status each of those jobs is at: "complete" or "in progress".


I can count all the jobs that are either "complete" or "in progress" using COUNTIF but how can I count jobs which are assigned to a certain person and are either "complete" or "in progress"? In other words, how can I count cells matching two criteria (name and status)?

I've tried nesting the COUNTIF in various logic statements but I'm not getting any closer to my goal. HELP!

 

[xlbo]

You need either a Database formula (over to you Dale) or an array formula. Both have their plus and minus points. array formulae are quicker to set up initially but too many will be sloooooooow. Database formulae are more time consuming to set up but do not seem to be affected by quantity.

anyways
suggest you give names to your data so
The column in your dataset with the peoples names in will be called dbName and the one with job status will be dbStatus
where you have people's names in A2:Awhatever B1 is a header "Complete" and C1 is header "In Progress"
In B2, enter:
=SUM((dbName=$A2)*(dbStatus=B$1))
and copy this across to C2 then copy down to the bottom of your list of names Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[xlbo]

d'OH - forgot to mention that the formula MUST be entered with
CTRL+SHIFT+ENTER instead of just ENTER. Anyways, { } should appear on either side of the formula once it is entered in this way Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[RivetHed]

A much easier way to do this is to concatenate the two values together into one string and then use countif to look for that

 

[xlbo]

No offence intended RivetHead but define "easier"
If the length of data changes, you need to make sure that the formula continues down to the correct row AND you are adding to the size of the dataset + you still need formulae in your summary table
With the array formula, you just have the formulae in the summary and all you have to do is remember to use CTRL+SHIFT+ENTER Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[RivetHed]

Apologies Geoff, not reading too closely, need another coffee I think.

 

[jonschofield]

Geoff

Thanks for the help...so far!

My intention is to have a summary sheet showing how many jobs aperson has either "in progress" or "complete" at one time. This means the actual data and the summary are seperate sheets in the same workbook. I've tried implementing your suggestion whilst adapting it to my specific sheet but it just shows "#NAME?" in the cell. Any suggestions?

 

[xlbo]

You need to have 2 named ranges:
dbName is a named range which covers the the cells that have people's names in in your dataset
dbStatus is a named range which covers the cells that hold the project status

Lets say that the dataset is held in a sheet called "Data"
Peoples names are in colA (2:1000) and status is in col H(2:1000)
your summary sheetr is called "summary" and has a list of unique names in A2:A10
In B1 enter "Complete".InC1, enter "In Progress"
Without named ranges, your formula (on summary sheet B2) would be:
=SUM((Data!A2:A1000="Complete&quot*(Data!H2:H1000="In Progress&quot)

Alol I have done is subbed a range name for the range ref and a range ref for the text to compare
HTH
Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Zathras]

Why not just use a pivot table?

I imagine a three column worksheet with Job, Person, Status as the headings.

Person down the side, Status across the top, count Jobs.

 

[xlbo]

Zathras - very true and good point - guess it depends on if there is anything else in the summary sheet etc Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[jonschofield]

Thanks for all the help everyone! The pivot table suggestion seems to do what I need at the moment and is very easy to manipulate.