Array formula to count cells matching various texts

[pdanes]

Apologies if this has been answered, but I couldn't find anything quite suiting what I need.

I have a set of cells that may contain numeric values, or letters indicating something specific. 8 = 8 hours worked, A = absence, V = vacation, P = paid leave and so on. I'm trying to sum and count the various values. I have a worksheet function the does the job, but it's messy, and I'd like a cleaner version. What I have now is a series of CountIfs: CountIf(a1:a10,"A") + CountIf(a1:a10,"V") + CountIf(a1:a10,"P").

I found a post in another forum showing how to do this with an array formula, like this: =IF(OR(A3=1, A3=4, A3=7, A3=10),1,0) can be replaced by this: =(OR( A1 = {1,4,7,10} ) ) * 1

However, I have been unable to adapt this to my own use. I either get errors or wrong values. What I am trying to build is something like this: CountIf(or( J10:AN10 = {"N";"O";"P";"V";"D";"A";"S"} ))

I have entered many version of it, as a normal formula and array formula, neither works. Is this even possible?

Pete

[Tony Valko]

One way...

=SUM(COUNTIF(J10:AN10,{"N";"O";"P";"V";"D";"A";"S"}))

[pdanes]

Yes, that did it. Thank you. Is there also a way to put the internal array into a named cell and use that as the parameter to CountIf? I've read about ways to do that, but none of the syntaxes I've tried have worked.

Instead of =SUM(COUNTIF(J10:AN10,{"N";"O";"P";"V";"D";"A";"S"})), can I do something like ={"N";"O";"P";"V";"D";"A";"S"} into a cell, name the cell (say, Fred), and use =SUM(COUNTIF(J10:AN10,Fred))?

[Tony Valko]

You can put the values in individual cells like:

A1 = N
A2 = O
A3 = P
A4 = V
A5 = D
A6 = A
A7 = S

Give the range a defined name like Fred.

Then use this formula:

=SUMPRODUCT(COUNTIF(J10:AN10,Fred))

[pdanes]

Great. That works very nicely - appreciate it. I've been trying to learn how to work with arrays, since they're so useful in so many situations, but the syntax is often puzzling, and error messages unhelpful. I'll keep working on getting a grip on the array method, but this solves my current problem. Many thanks.

Pete

[Tony Valko]

You're welcome. Thanks for the feedback!

If your question has been answered please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools>Mark this thread as solved.

See if this helps wrt array formulas:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

[pdanes]

Already marked, and reputation added. Thanks for the link - I've known about Chip's site for years, and used it a lot, but I missed it this time around. Got a bit of reading and experimenting for this evening.