Search code examples
google-sheetsgoogle-sheets-formula

Is it ArrayFormula I want?

What I want is in the data tab, cell B10, to be a count of the number of times a certain person will appear in any of three different columns in the ALT_ELA tab (J, X, and AL, you'll see them in the COUNTIFS formula that I have in there, but that's not giving me what I need, since that's asking for rows in which the name occurs in all three places).

So in column B i want the count of total places that person is assigned, and then in column C I want to track the number of 1s that that person has earned.

So if a name exists in J, X, and/or AL, how many times, and then how many 1s has that person earned in either N, AB and/or AP.

Thanks for any help!

Solution

  • Based on the article What Does the COUNTIFS Function Do in Google Sheets?, the countifs() function will only count if all the criteria have been satisfied. In the sheets provided, the columns J, X, AL didn't have the value "Craig" which was why you were getting 0.

    You can try this syntax in cell B10, so that you can sum up the specific name in column J,X,AL (the criterion is based on the name provided in Column A-Contributors):

    =SUM(COUNTIF(ALT_ELA!J:J,A10),COUNTIF(ALT_ELA!X:X,A10),COUNTIF(ALT_ELA!AL:AL,A10))

    enter image description here

    Then on column C, to track the 1s on columns N, AB and/or AP, you can use the query() function with sum():

    =SUM(QUERY(ALT_ELA!A:AP,"Select N,AB,AP where (J = '"&A10&"' or X = '"&A10&"' or AL = '"&A10&"')",0))

    enter image description here

    Note: I have tried adding 1s and names on the mentioned columns so the result in the screenshots may not be aligned with yours.