Q: I need to count the number of times an item occurs in column a if another thing also occurs in column b on the same row. I don’t think countif can be used for this can it?

A: You are correct in that countif cannot be used for two arguments in that manner. The simplest solution to your problem is to use the sumproduct array:

=sumproduct((A2:A100=”Criteria 1″)*(B2:B100=”Criteria 2″))

This will check a2:a100 for your first criteria and if criteria 2 is found in the corresponding cell (in b2:b100) it will count as 1.

Joseph Harris
Excel Resources
Blogging Site

Related posts:

  1. Sum by the Color of a Cell – Excel Surfing the web today, I came across a great user...
  2. Vlookup Multiple Criteria in Excel The standard vlookup function allows for one condition to be...

This entry was posted on Thursday, December 4th, 2008 at 1:56 pm and is filed under Mail Bag. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.