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:
- Sum by the Color of a Cell - Excel Surfing the web today, I came across a great user...
- Vlookup Multiple Criteria in Excel The standard vlookup function allows for one condition to be...