# Thread: countif based on two columns (excel 2000)

1. ## countif based on two columns (excel 2000)

Hi,
In my spreadsheet, I'm trying to calculate the percentage of jobs in a certain category that are not completed.
I have columns for category and for signoff.
I've used Countif to count the number of jobs in the category but don't know how to count "jobs where category =wp03 and signoff=blank"
Could someone please point me to the function to use?
Thanks in advance,
Judith

2. ## Re: countif based on two columns (excel 2000)

Try this function Judith

{=SUM(IF(B2:B6="wp03",IF(ISBLANK(C2:C6),1,0),0))} : based on attached sample!

See attachment!

3. ## Re: countif based on two columns (excel 2000)

Thanks Rudi,
The function is brilliant.
I've even expanded it to match 3 criteria, one of which is "not blank".
I didn't know the cntl-shift-enter trick for arrays.

Many thanks,

4. ## Re: countif based on two columns (excel 2000)

I've been playing around with some other examples and came up with:

{=SUM((B2:B6="wp03")*ISBLANK(C2:C6))} also arrayed using CTRL+SHIFT+ENTER
and
=SUMPRODUCT((B2:B6="wp03")*ISBLANK(C2:C6)). There is no need to array the function here! Simplifies the matter!

5. ## Re: countif based on two columns (excel 2000)

I like the idea of the product. I'll be leaving this spreadsheet for my successor and the product method seems more robust for when he or she wants to fiddle with it.
Thanks again

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•