# Thread: countif with 2 criteria (2003)

1. ## countif with 2 criteria (2003)

Is there any way to formulate what I have done by hand in the sample attachment? On the data tab I have a list of names and what type of sale each line was (they get entered by date the sale was made). On the results tab, it lists each name and counts for each type of sale (summary). Any way to do this with a formula?

2. ## Re: countif with 2 criteria (2003)

A pivot table (Data | Pivot Table and Pivot Chart Report) is a good way to do this.
Formulas with SUMPRODUCT are another way.
See attached workbook.

3. ## Re: countif with 2 criteria (2003)

Apologies in advance for not being able to read the 03 attachment - here in the sticks I'm celebrating 10 years of office 97, still going strong (ok, maybe not so strong).
But, in answer to the post, you could always use an array formula such as:
sum(if(a1:a100="ford",if(b1:b100="blue",1,0),0))
and use ctrl+shift+enter, then you'll get a count of all blue fords from columns A and B

Sorry if it's off topic from the content of the attachment.

alan
cheshire

4. ## Re: countif with 2 criteria (2003)

I think you should be able to open the attachments in this thread - the file format for Excel 2003 is the same as that for Excel 97.

5. ## Re: countif with 2 criteria (2003)

Wow, haven't had much (none actually) encounters with 2003 but again you're spot on Hans.
Attached file has array formulas in results table.

Cheers
Alan
Cheshire

6. ## Re: countif with 2 criteria (2003)

Another possible array formula is (here for Jack / Corporate):

=SUM((\$A\$2:\$A\$11=\$D11)*(\$B\$2:\$B\$11=E\$10))

7. ## Re: countif with 2 criteria (2003)

From little acorns mighty oaks et cetera.
It never ceases to amaze me that every time I come through the lounge I learn a little something, and that formula kind of switches the light in another direction.

gratefully
Alan
Cheshire

8. ## Re: countif with 2 criteria (2003)

This works great!! I also have some columns that have dollar figures in them. Example: I want to sum column C based on criteria being met in column a and column b. The sumproduct works great to count items, but any ideas how to sum them?

9. ## Re: countif with 2 criteria (2003)

You can simply add column C to the formula, e.g.

=SUMPRODUCT((Data!\$A\$2:\$A\$11=Results!\$A4)*(Data!\$B \$2:\$B\$11=Results!B\$3)*Data!\$C\$2:\$C\$11)

See attached workbook (I modified the pivot table too.)

10. ## Re: countif with 2 criteria (2003)

This worked, but seems to be limited to the number of records that can be used. Here is what I used and got the #Value error.

=SUMPRODUCT((Data!\$A\$4:\$A\$7000=Results!\$A4)*(Data! \$B\$4:\$B\$7000=Results!B\$3)*Data!\$C\$4:\$C\$7000)

I reduced the 7000's to 50's and it worked fine. Any thoughts??

11. ## Re: countif with 2 criteria (2003)

The formula should work with large ranges too. Make sure that there are no text entries in C4:C7000 - not even entries consisting of spaces only, or formulas resulting in "".

12. ## Re: countif with 2 criteria (2003)

As always, you hit the nail right on the head. It was a calculated field that displayed "" until a record was entered on that line to calculate an acutal dollar figure. I simply changed "" to a 0 in my formula. All good!!

Thanks for the help!

#### Posting Permissions

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