# Thread: COUNTIF (2003)

1. ## COUNTIF (2003)

Hi everyone,

I am try to count all of the fields in colomn E if they meet certain criteria but only if colomn D meets certain criteria in the same row

Heres where I'm at so far

=COUNTIF(Sheet1!E:E,"A")+CountIF(Sheet1!E:E,"B") >>>>>I there a way to use AND/OR in this formula sort of like =COUNTIF(Sheet1!E:E,"A"Or"B")

The above works for counting "A" and "B" from Sheet 1 E:E but...

Now I need to count "C" and "D" but only if Sheet 1, coloum D on the matching row = "Internal Incomplete"

I tried =IF(Sheet1!D="Internal Incomplete",(COUNTIF(Sheet1!E:E,"C"))) >>>>> Did not work

Many thanks

2. ## Re: COUNTIF (2003)

SUMIF and COUNTIF do not support multiple conditions (Excel 2007 has new functions SUMIFS and COUNTIFS that do). You can use SUMPRODUCT instead:

=SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*(Sheet1!E1:E1000="C"))

(This does not work with entire columns D and E:E, you have to specify finite ranges)

3. ## Re: COUNTIF (2003)

Thanks Hans

=SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*(Sheet1!E1:E1000="C"Or"B"))

4. ## Re: COUNTIF (2003)

Is that a question?

5. ## Re: COUNTIF (2003)

I think your question how to make this work:
=SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*(Sheet1!E1:E1000="C"Or"B"))

So I would answer it with:

=SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*((Sheet1!E1:E1000="C") +(Sheet1!E1:E1000="B")))

The "*" acts as an "AND" and the "+" acts like am "OR"

Steve

6. ## Re: COUNTIF (2003)

Alternatively:
<code>=SUMPRODUCT((Sheet1!D11000="Internal Incomplete")*(Sheet1!E1:E1000={"C","B"})) </code>

7. ## Re: COUNTIF (2003)

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I think it should be:

=SUM((D11000="Internal Incomplete")*<font color=red>(</font color=red>(E1:E1000="C")+(E1:E1000="B")<font color=red>)</font color=red>)

Your formula will calculate for (D="Internal Incomplete" AND E= "C") OR just E="B". If E= B then it would not matter what column D has in it....

Steve

8. ## Re: COUNTIF (2003)

Yep, you are correct, I hadn't tested it fully with a mixture of letters,

I'll change mine now

9. ## Re: COUNTIF (2003)

<P ID="edit" class=small>(Edited by Jezza on 20-Aug-08 16:23. To add parenthesis, as per Steve's message below)</P>As an another alternative using the sum function as an array formula try:

=SUM((D11000="Internal Incomplete")*((E1:E1000="C")+(E1:E1000="B")))

and then press Ctrl+Shift+Enter whilst the cell that holds this formula has the focus. It should look like this afterwards:

{=SUM((D11000="Internal Incomplete")*((E1:E1000="C")+(E1:E1000="B")))}

This is an unusual anomaly as using sum in this way actual converts it to counting....enjoy <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

#### Posting Permissions

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