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.
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”).
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.
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.
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.
At least two issues:
OR can't be iterated over an array in an array expression; you will need to use the + operand instead.
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)))))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With