Countif forumulas that refer to more than one column

[Guest]

Hello,

I’m 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.

I’ve had a few goes, without success, can anyone help?

All and any help much appreciated.

Thanks

Karl

 

[Guest]

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

 

[Guest]

SUMPRODUCT is the function you need here:
=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.

 

[Guest]

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,

 

[Guest]

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.