Complex COUNTIF

[bkelly]

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.

Thanks for your time

 

[NBVC]

Try using Sumproduct

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

 

[Rijnsent]

Try this one:

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

Greetz,

Koen

 

[NBVC]

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"))

 

[bkelly]

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

 

[NBVC]

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.