Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

False return of 1 on COUNTA with FILTER

I am trying to make a sheet to count the number of instances of data and certain responses are going to be grouped together. I am having problems coming up with the three formulas correctly. Here are my best guesses along with some English to help clarify what I am trying to accomplish.

//Count all cells that are Warrior or Paladin [Always returns 1, and not zero]
=COUNTA(FILTER(I:I, OR(I:I="Warrior", I:I="Paladin")))

//Count all cells that are Scholar or White Mage [Always returns 1, and not zero]
=COUNTA(FILTER(I:I, OR(I:I="Scholar", I:I="White Mage")))

//Count all cells that are do not all within the first sets of requirements [Always returns 1, not 2]
=COUNTA(FILTER(I:I, NOT(OR(I:I="Warrior", I:I="Paladin", I:I="Scholar", I:I="White Mage"))))

The two cells are Monk and Summoner. Any help would be appreciated

Edit: Here is a sample spreadsheet.

like image 341
Blaze Phoenix Avatar asked Aug 12 '14 04:08

Blaze Phoenix


People also ask

Can you combine Countif and Counta?

We can use a combination of the COUNTA, COUNTIF, and SUMPRODUCT functions to get the desired results. We can list down the things we wish to exclude from counting. One other way to arrive at the same result is to use the formula =COUNTIFS(B4:B9,”<>Rose”B4:B9,”<>Marigold”).

Why is Counta including blank cells?

COUNTA counts cells that contain 'something'. Each of those 'blank' cells contains a formula. Each formula returns a result. The result in those blank cells is most likely a null string ( "" ), which has an appearance similar to that of an empty cell.

Which of the following is the correct output of Counta function?

The formula “=COUNTA(A1,A2,A3)” returns 2. The COUNTA function can count cells containing several types of data values. This includes text, numbers, Boolean values, date/time values, error values, and empty text strings (“”). It returns a numeric value.

What is the formula if you count the number of cells that are not empty the range of cells starts from cell A2 to a100?

The COUNTA function in Excel counts cells containing any value, i.e. cells that are not empty. The syntax of the Excel COUNTA function is akin to that of COUNT: COUNTA(value1, [value2], …) Where value1, value2, etc.


1 Answers

At least two issues:

  1. OR can't be iterated over an array in an array expression; you will need to use the + operand instead.

  2. FILTER will return #N/A if there is no output, and COUNTA will count this error value as 1. Hence when 0 is expected, you get 1; use IFERROR to account for this.

=COUNTA(IFERROR(FILTER(I:I,(I:I="Warrior")+(I:I="Paladin"))))

=COUNTA(IFERROR(FILTER(I:I,(I:I="Scholar")+(I:I="White Mage"))))

=COUNTA(IFERROR(FILTER(I:I,I:I<>"Warrior",I:I<>"Paladin",I:I<>"Scholar",I:I<>"White Mage")))

Alternatives to the first and third formulae that are more easily extendible:

=COUNTA(IFERROR(FILTER(I:I,MATCH(I:I,{"Warrior";"Paladin"},0))))

=COUNTA(IFERROR(FILTER(I:I,ISERROR(MATCH(I:I,{"Warrior";"Paladin";"Scholar";"White Mage"},0)))))

like image 58
AdamL Avatar answered Nov 09 '22 20:11

AdamL