# Complex COUNTIF

#### bkelly

##### Active Member
My spreadsheet uses the following formula

=COUNTIF(D3:D84,"1")

This works, but I need to add some more qualifications. Try as I might, I cannot find the right syntax. Here is what I need:

=COUNTIF( (D3:D84,"1") AND (F3:F84="Tom") AND (I3:I84="OPEN" ) )

Goal: This is a spreadsheet of issues (problems). This example is to count the number of priority 1 issues assigned to Tom that are open.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try using Sumproduct

=SUMPRODUCT(--(D3:D84,1),--(F3:F84,"Tom"),--(I3:I84,"OPEN"))

Try this one:

=SUMPRODUCT((D3:D84=1)*(F3:F84="Tom")*(I3:I84="OPEN"))

Greetz,

Koen

NBVC said:
Try using Sumproduct

=SUMPRODUCT(--(D3:D84,1),--(F3:F84,"Tom"),--(I3:I84,"OPEN"))

Sorry, was thinking of countif at same time:

Should be
=SUMPRODUCT(--(D3:D84=1),--(F3:F84="Tom"),--(I3:I84="OPEN"))

I grabbed the one from NVBC, pasted it in, and (of course) it worked.
thank you.

BTW: what is the purpose of the two dashes in each segment?

Thanks again

bkelly said:
I grabbed the one from NVBC, pasted it in, and (of course) it worked.
thank you.

BTW: what is the purpose of the two dashes in each segment?

Thanks again

If you search for "coersion" in this site, I am sure you will find some good explanations, but basically it forces True/False to quickly convert to 1 and 0, respectively for more efficient counting (or summing) when processing the formula.

Replies
5
Views
337
Replies
1
Views
155
Replies
3
Views
159
Replies
3
Views
272
Replies
2
Views
315

1,196,426
Messages
6,015,187
Members
441,882
Latest member
LostinExcelHelp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back