[Google Spreadsheet][COUNTIFS] selecting values from comma / non-comma separated list

1
votes

From the below data, I need to count all the 2s that are closed. I tried using COUNTIFS but it counts the column with comma separated values but not the non-comma columns. Here's the formula that I used:

=COUNTIFS(A:A, "*2*", B:B, "closed")

This returns value of 2 instead of 4.

  A      B   
-----  ----- 
1,2,3   open  
2       closed
2       closed
1,3     open
2,3     closed
1,2     closed
2       open
1
excelgoogle-sheets

1 Answers

1
votes

It's only counting the cells in column a that contain "2" as text i.e.

2,3

and

1,2

It doesn't count the cells that contain the number 2 on its own.

You could force all the cells to be taken as text like this:-

=ARRAYFORMULA(COUNTIFS(""&A1:A10,"*2*",B1:B10,"Closed"))

Another way is:-

=DCOUNTA(A1:B10,1,{"A","B";2,"closed";"*2*","closed"})

assuming that A and B are the column headers.

But easier just to count the numbers and text separately:-

=COUNTIFS(A:A, "*2*", B:B, "closed")+COUNTIFS(A:A, 2, B:B, "closed")

Also you can't do the first one in Excel as far as I know without using a helper column and can't do the second one without setting up the criteria in the sheet somewhere.