Amiga.org

Coffee House => Coffee House Boards => CH / General => Topic started by: motorollin on January 07, 2011, 08:18:38 AM

Title: Excel: Using COUNTIF to match values in multiple columns
Post by: motorollin on January 07, 2011, 08:18:38 AM

Thought I'd ask here before looking for an Excel forum. I need to use COUNTIF (or similar) to check the values of multiple columns. Example data (in code tags to preserve spacing):

Code: [Select]


ROW     SCHOOL     ACTIONS
1       School A   Yes
2       School A   No
3       School A   Yes
4       School B   Yes
5       School B   No


How could I return, e.g., the number of rows in which SCHOOL="School A" and ACTIONS="Yes" (i.e. 2)? I have found various methods of embedding count statements, but I can't quite get the syntax right and I think they only work in these examples when checking numerical values.

Title: Re: Excel: Using COUNTIF to match values in multiple columns
Post by: motorollin on January 07, 2011, 09:03:31 AM

Ok, figured it out. You use SUMPRODUCT, which I did try, but didn't realise that it doesn't work with a whole column range (e.g. A:A), so I did it like this:

=SUMPRODUCT((A1:A10000="School A")*(B1:B10000="Yes"))

Not very efficient as presumably it's checking all of those empty rows, but I don't want to miss data as the spreadsheet expands.