0

I'm trying to count the number of cells which contain either a N or a A in one column and a Y in another column.

I can get it to check for one value in each column (N in colB and Y in colJ)

=SUM(COUNTIFS('Nov-Jan'!B$3:B$60,"N", 'Nov-Jan'!J$3:J$60,"Y"))

But i cant get it to match for either a N or A in colB and a Y in colJ. Tried this but it doesnt work:

=SUM(COUNTIFS('Nov-Jan'!B$3:B$60,"N",'Nov-Jan'!B$3:B$60,"A", 'Nov-Jan'!J$3:J$60,"Y"))

Is there anything I'm doing wrong/can change to get the same outcome? I looked at the OR function but apparently it ignores text based values.

Simon
  • 133
  • 1
  • 6
  • 15

1 Answers1

1

First of all the SUM in your function does nothing. You can leave it out. Then first solution is to add up two countifs

=COUNTIFS('Nov-Jan'!B$3:B$60,"N", 'Nov-Jan'!J$3:J$60,"Y")+COUNTIFS('Nov-Jan'!B$3:B$60,"A", 'Nov-Jan'!J$3:J$60,"Y")

Because countifs cant handle OR operation you need to do it in two steps. Or you can use SUMPRODUCT, because it can handle OR:

=SUMPRODUCT((('Nov-Jan'!B$3:B$60="A")+('Nov-Jan'!B$3:B$60="N"))*('Nov-Jan'!J$3:J$60="Y"))

For the SUMPRODUCT you only need to make sure you have brackets correct and any "+" is as OR operator and "*" as AND.If you wanted to Add the column C with AND operator, you would do

=SUMPRODUCT((('Nov-Jan'!B$3:B$60="A")+('Nov-Jan'!B$3:B$60="N"))*(('Nov-Jan'!C$3:C$‌​‌​60="A")+('Nov-Jan'!C$3:C$60="N"))*('Nov-Jan'!J$3:J$60="Y"))

with OR it is as you wrote it in comment

Pavel_V
  • 1,220
  • 1
  • 11
  • 17
  • Thanks @Pavel_V. If i wanted to extend the comparison to another column (colC) to look for N and A how would i use sumproduct? =SUMPRODUCT((('Nov-Jan'!B$3:B$60="A")+('Nov-Jan'!B$3:B$60="N")+('Nov-Jan'!C$3:C$‌​60="A")+('Nov-Jan'!C$3:C$60="N"))*('Nov-Jan'!J$3:J$60="Y")) – Simon May 04 '16 at 12:48
  • As you wrote it it would find all the lines that have Y in J column and (A in B or N in B or A in C or N in C). it would not matter if "A" or "N" appeared in column B or C. As long as it was in one, it would count the line. – Pavel_V May 04 '16 at 12:54