Welcome, Guest. Please login or register.

Author Topic: Excel: Using COUNTIF to match values in multiple columns  (Read 2771 times)

Description:

0 Members and 1 Guest are viewing this topic.

Offline motorollinTopic starter

  • Hero Member
  • *****
  • Join Date: Nov 2005
  • Posts: 8669
Excel: Using COUNTIF to match values in multiple columns
« 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.
Code: [Select]
10  IT\'S THE FINAL COUNTDOWN
20  FOR C = 1 TO 2
30     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NAAAA
40     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NA-NA-NAAAAA
50  NEXT C
60  NA-NA-NAAAA
70  NA-NA NA-NA-NA-NA-NAAAA NAAA-NAAAAAAAAAAA
80  GOTO 10
 

Offline motorollinTopic starter

  • Hero Member
  • *****
  • Join Date: Nov 2005
  • Posts: 8669
Re: Excel: Using COUNTIF to match values in multiple columns
« Reply #1 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.
Code: [Select]
10  IT\'S THE FINAL COUNTDOWN
20  FOR C = 1 TO 2
30     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NAAAA
40     DA-NA-NAAAA-NAAAA DA-NA-NA-NA-NA-NA-NAAAAA
50  NEXT C
60  NA-NA-NAAAA
70  NA-NA NA-NA-NA-NA-NAAAA NAAA-NAAAAAAAAAAA
80  GOTO 10