I'm sorting linguistic data, and I need to count cells within a range that do NOT contain ANY of four specific entries. In addition, I need to check these against one further criterion in another column before counting them. E.g, it would be like counting lines that have an x in column A, AND do NOT have an a, b, c, OR d in column B. (And better than a simple count, I'd love to have an array of 1s and 0s telling where the conditions are met.)
I am completely new to array formulas in Excel (though have some background in other types of programming). Just as a test to see how the array calculations work, I've been trying this formula.
I set up a 'testrange' that contains the following entries:
Then I'm trying to run this formula, using ctl-shift-enter:
[code]
{=IF(testrange='B',1,0)}
[code]
I expected to get an array that looks like this:
[0
1
0
0
0
0]
Or failing that, at least a 1, because the range contains a match.
What I get instead is just a scalar 0. If I change the formula to read
[code]
{=IF(testrange='A',1,0)}
[code]
then I get a 1 as output. In other words, it appears to test only the first cell in the range. Is this a strange MAC issue, or just how this command works? I tried duplicating the formula alongside each of the entries, but I still got only zeros. Is anyone able to explain how I could get an array showing where the condition is true as output? In the 'real world' I'm working with about 1500 lines of data spread across 6 worksheets, so I want to know what I'm doing before I start trying to interpret output.
Many thanks for your help, and as this is my first post, please graciously let me know if it's over/under detailed, or unclear!