0

I'm having some issues with a formula. In the formula I'm trying to sum the number of items in column E based upon the condition in column C.The formula I've used is, (which is working correctly.)

=SUM(COUNTIFS('Sheet1'!E:E,C86,'Sheet1'!C:C,{"A","B","C","E1","E2","F","G","X","T"}))

However in the next cell I'm trying go a bit further and trying to count the number of "Yes" and "NA" in column R based upon the condition in column E and Column C. The formula I've used is - 

=SUM(COUNTIFS('Sheet1'!E:E,C86,'Sheet1'!C:C,{"A","B","C","E1","E2","F","G","X","T"},'Sheet1'!R:R,{"Yes","NA"}))

This time the formula is counting only "A" in column C and "Yes" in column R. It'll be great help if someone can point what mistake I'm making with this formula.

Thanks in advance!

Best Regards,

gmainak

gmainak
  • 1
  • 1

1 Answers1

0

Change 'Sheet1'!R:R,{"Yes","NA"} to 'Sheet1'!R:R,{"Yes";"NA"}

You can only do two arrays and one needs to be vertical ; and the other horizontal ,:

=SUM(COUNTIFS('Sheet1'!E:E,C86,'Sheet1'!C:C,{"A","B","C","E1","E2","F","G","X","T"},'Sheet1'!R:R,{"Yes";"NA"}))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81