Hello,

Im trying to setup a worksheet which my department can use to track how
many jobs of each different kind it has on the go.

At the top of the worksheet, I want to build a section showing the number of
jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs
and so on).

To do this, I have to get the cells in question to look at two columns. The
first column is a validated list of job types (copywriting, editing, subbing,
proofing). The second column shows the status of the job (awaiting
commission, in progress, submitted, approved).

What I want to do is write a formula that says the following:

Look a column b, if the value is €œawaiting commission€� or €œin progress€�,
check column a. If the value in column a is (for instance) €œproofing€�, count
that cell.

Ive had a few goes, without success, can anyone help?

All and any help much appreciated.

Thanks

Karl

You need to use SUMPRODUCT, not COUNTIF to count based on multiple criteria.
A formula like =SUMPRODUCT(--(A1:A10="awaiting
commission"),--(B1:B10="proofing")), which will count all records for which
column A has "awaiting commission" and column B has "proofing".

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Karl" wrote:

Hello,

Im trying to setup a worksheet which my department can use to track how
many jobs of each different kind it has on the go.

At the top of the worksheet, I want to build a section showing the number of
jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs
and so on).

To do this, I have to get the cells in question to look at two columns. The
first column is a validated list of job types (copywriting, editing, subbing,
proofing). The second column shows the status of the job (awaiting
commission, in progress, submitted, approved).

What I want to do is write a formula that says the following:

Look a column b, if the value is €œawaiting commission€� or €œin progress€�,
check column a. If the value in column a is (for instance) €œproofing€�, count
that cell.

Ive had a few goes, without success, can anyone help?

All and any help much appreciated.

Thanks

Karl

SUMPRODUCT is the function you need he
=SUMPRODUCT((A1:A5="writing")*(B1:B5="awaiting"))

Change the address ranges and text to match the pairings you want to count.
You can even use cell addresses in place of the "writing" and "awaiting"
portions of the formula, so you could have a pair of cells to choose them
from to have a single place to show counts based on what you have in them.

"Karl" wrote:

Hello,

Im trying to setup a worksheet which my department can use to track how
many jobs of each different kind it has on the go.

At the top of the worksheet, I want to build a section showing the number of
jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs
and so on).

To do this, I have to get the cells in question to look at two columns. The
first column is a validated list of job types (copywriting, editing, subbing,
proofing). The second column shows the status of the job (awaiting
commission, in progress, submitted, approved).

What I want to do is write a formula that says the following:

Look a column b, if the value is €œawaiting commission€� or €œin progress€�,
check column a. If the value in column a is (for instance) €œproofing€�, count
that cell.

Ive had a few goes, without success, can anyone help?

All and any help much appreciated.

Thanks

Karl

Hi Karl

Sir Jlatham, hello again...

Without mentioning how the "status and data" on both column has to be
encoded either by a List or manual typing...
I would prefer to suggest a Pivot Table...for tracking of counted records...

Yet, I still do not know where is the official forum group in excel to
source for good help on Pivot table....i hope someone can clarify this to
us...

regards,

--
*****
birds of the same feather flock together..



"JLatham" wrote:

SUMPRODUCT is the function you need he
=SUMPRODUCT((A1:A5="writing")*(B1:B5="awaiting"))

Change the address ranges and text to match the pairings you want to count.
You can even use cell addresses in place of the "writing" and "awaiting"
portions of the formula, so you could have a pair of cells to choose them
from to have a single place to show counts based on what you have in them.

"Karl" wrote:

Hello,

Im trying to setup a worksheet which my department can use to track how
many jobs of each different kind it has on the go.

At the top of the worksheet, I want to build a section showing the number of
jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs
and so on).

To do this, I have to get the cells in question to look at two columns. The
first column is a validated list of job types (copywriting, editing, subbing,
proofing). The second column shows the status of the job (awaiting
commission, in progress, submitted, approved).

What I want to do is write a formula that says the following:

Look a column b, if the value is €œawaiting commission€� or €œin progress€�,
check column a. If the value in column a is (for instance) €œproofing€�, count
that cell.

Ive had a few goes, without success, can anyone help?

All and any help much appreciated.

Thanks

Karl

I'm not sure which one would be best to go to with specific Pivot Table
questions - I suspect this one is close to being it. But I work so little
with Pivot Tables myself that when I realize I need one, I have to drag out
the books to get it done. The other side of that coin is that by not using
the feature often, it is easily overlooked by me as a potential solution.

"driller" wrote:

Hi Karl

Sir Jlatham, hello again...

Without mentioning how the "status and data" on both column has to be
encoded either by a List or manual typing...
I would prefer to suggest a Pivot Table...for tracking of counted records...

Yet, I still do not know where is the official forum group in excel to
source for good help on Pivot table....i hope someone can clarify this to
us...

regards,

--
*****
birds of the same feather flock together..



"JLatham" wrote:

SUMPRODUCT is the function you need he
=SUMPRODUCT((A1:A5="writing")*(B1:B5="awaiting"))

Change the address ranges and text to match the pairings you want to count.
You can even use cell addresses in place of the "writing" and "awaiting"
portions of the formula, so you could have a pair of cells to choose them
from to have a single place to show counts based on what you have in them.

"Karl" wrote:

Hello,

Im trying to setup a worksheet which my department can use to track how
many jobs of each different kind it has on the go.

At the top of the worksheet, I want to build a section showing the number of
jobs outstanding by type (so, for instance, 2 writing jobs, 3 proofing jobs
and so on).

To do this, I have to get the cells in question to look at two columns. The
first column is a validated list of job types (copywriting, editing, subbing,
proofing). The second column shows the status of the job (awaiting
commission, in progress, submitted, approved).

What I want to do is write a formula that says the following:

Look a column b, if the value is €œawaiting commission€� or €œin progress€�,
check column a. If the value in column a is (for instance) €œproofing€�, count
that cell.

Ive had a few goes, without success, can anyone help?

All and any help much appreciated.

Thanks

Karl