Google-sheets – How to create a IF formula that searches for multiple (more than three) text options

google sheetsgoogle-sheets-arrayformula

I'm trying to create a Google Sheet to track pitches (I'm a baseball coach). I have a current sheet that I have a check box to click if the pitch was a strike or not, and then you have to choose a "result" like a swinging strike or a foul ball or a hit ball. I was wondering if there was a formula that could search for any of those "results" and produce a true false or a 1, 0. Something like:

=If(A1, "take strike" or "swinging strike" or "foul ball" or "hit ball"), 1, 0)

There will be a total of 14 "results" I want to include in this formula to count all possible "strike" calls. I have other columns set up to total strike and balls based on 1/False. But trying to take one less step out.

I have more things I'd like to do with this sheet and will be back with other questions, but that was my first one.

Best Answer

How about this?

=IF(OR(A1 = "take strike", A1 = "swinging strike", A1 = "foul ball", A1 = "hit ball"),1,0)

Formula

=QUERY(Sheet1!A1:C9;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A PIVOT C")

then the table will appear as you want.

Explained

Here an explanation of the formula, from the inside to the outside:

the PIVOT C transposes the unique results from column C (turning rows into columns)
the GROUP BY A only filters out unique values in column A. (the same as sheet TABLE!A2)
the WHERE B IS NOT NULL ignores empty cells
the SUM(B) adds the result of the query
the SELECT A simply displays column A, as unique values

If you're only interested in individual results, then I suggest using the following formula:

=IFERROR(SUM(FILTER(DATA!$B$2:B;DATA!$C$2:C=$B$1;DATA!$A$2:A=A2));"")

Here an explanation of the formula, from the inside to the outside:

the FILTER function will retrieve the amounts for range B2:B, column B without the header, by filtering range B2:B for "No" and range C2:C for "Yes".
the SUM function will add them together.
the IFERROR will leave a blank cell if an error occurred (not result).

Screenshot

Example

See example file I've prepared, where both samples are present: SUM data based on two variables

Note

The formula in the example file is a bit different (the range is set to contain complete columns).

Google Sheets – Calculate Conditional Averages with Functions

Short answer

Instead of AVERAGEIF use an array formula as AVERAGEIF requires a range as it's third parameter.

Explanation

The below formula could be used to calculate the average for each category without having to use an auxiliary column and as it use open ended references, it will not require to be modified when new form responses be submitted.

=ArrayFormula(
  QUERY(
    FILTER(
      {'Sheet1'!A:A,'Sheet1'!B:B/'Sheet1!C:C},LEN('Sheet1'!A:A)
    ),
    "select Col1,AVG(Col2) group by Col1")
 )

Using the example source data provided by the OP, the result is the following:

+---+---+--------------+
|   | A |      B       |
+---+---+--------------+
| 1 |   | avg          |
| 2 | A | 0.3333333333 |
| 3 | B | 0.75         |
| 4 | C | 0.8333333333 |
| 5 | D | 0.875        |
| 6 | E | 0.7569444444 |
+---+---+--------------+

{'Sheet1'!A:A,'Sheet1'!B:B/'Sheet1!C:C} creates an array with two columns, the first one is the category column, the second calculates the dividend of Column B divided by Column C.
The FILTER function is used to remove blank rows.
The QUERY function is used to calculate the average for each category in the first column.

Best Answer

Related Solutions

Google Sheets – Create Formula to SUM Data Based on Two Variables