0

I have a large Excel dump from SQL with many columns of data. Two of those columns have different fields with various text values. There are six correct values for the first column and five correct values for the second column. I need to count the accounts (column A) that have both "correct" values.

Craig
  • 33
  • 2
  • 7

2 Answers2

1

Well, I just stumbled on this guy:

=COUNTIFS(A:A,"val1",B:B,"val2")

where: A:A is the first column you have. "Val1" is the valid value in the first column you want. B:B is the 2nd column you have. "Val2" is the valid value in the 2nd column you want.

Unfortunately, that only works if you have 1 value for each column .. and they work more like an AND .. not an OR.

So rather than that, I'd suggest a "helper column": 1) setup your list of valid values somewhere else, and name the lists: "validcol1" and "validcol2"

==IF(OR(ISERROR(MATCH(A2,validcol1,0)),ISERROR(MATCH(B2,validcol2,0))), "", "Valid!")
Ditto
  • 3,256
  • 1
  • 14
  • 28
  • Your first formula can work because the function allows for many criteria applied to ranges. – Pierre L Apr 21 '15 at 20:08
  • would you know how to ? when I mucked with it, it seemed to behave like an AND, not an OR .. so I'm not how to make it work :) Thought I'd put it out there in case others knew :) – Ditto Apr 21 '15 at 20:11
  • the OP asked for a formula for the `AND` condition. For `OR` conditions put a plus sign between two `COUNTIF` functions. Ex. `=COUNTIF(B2:B9,">5")+COUNTIF(B2:B9,">=15")` – Pierre L Apr 21 '15 at 20:23
0

I solved it through and array that uses "find."

{ =SUM(1*(IFERROR(FIND(b1:b413,"0B,D,E,K,L,S"),0)>0)*(IFERROR(FIND(c1:c413,"0ZA,ZB,ZC,ZF,ZK"),0)>1))}

Note the use of the leading 0 at the start of each set of values.

It's my understanding hard-coding the values into the formula isn't ideal. Anyone have any ideas for that?

Craig
  • 33
  • 2
  • 7