Search code examples
arrayscountgoogle-sheetsrowinstances

Google Sheets / Count instances in only odd rows of an array

I've been going around and around on this one.

I have an array that consists of one type of thing (like a header) on the odd rows and then the data on the even rows. Here's a 4x4 cell example (which really contains 8 headers and 8 data elements):

     A            B              C             D
 +---------------------------------------------------
1| *Red         *Blue          Blue          Blue
2| Robin        Sparrow        Oriole        Blue Jay
3| *Blue        Blue-xx        *Red          Red
4| Thrush       Barred Owl     Red Hawk      Eagle

I'm looking for a way to count only the

  • odd-row "blues" and "reds" (in two separate formulae)
  • that do NOT have as asterisk

It needs to be NOT tripped up by:

  • The additional "-xx" in B3.
  • The presence of the strings "blue" or "red" in non-header even cells (D2="BLUE jay"; B4="barRED owl"; C4="RED hawk")

Assume I already know through other methods that there were 5 blue and 3 red header values to begin with, all of which started with an asterisk. I would prefer for the solution to involve counting only those cells that have no asterisk, but because of the assumption I stated, it's ok for the solution to count only those that DO have an asterisk and then subtract.

Thus the "blue" formula should report that there are 3 odd-row "blues" without asterisks (C1, D1, B3).

The "red" formula should report that there are 2 odd-row "reds" without an asterisk (A1, C3).

Currently, I have in place this ugly thing:

=if({Five original blues})-(COUNTIF($A$1:$B$1,"blue")+countif($A$3:$B$3,"blue"))>0,{Five original blues}-(countif($A$1:$B$1,"blue")+countif($A$3:$B$3,"blue")),"Zero")

Or, parsing it out, if (5 - ((blues on line 1)+(blues on line 3)) is positive, then display that number. If it's not positive, write out the word zero.

=if(
     {Five original blues}) -  
     (COUNTIF($A$1:$B$1,"blue")+COUNTIF($A$3:$B$3,"blue"))
    >0
   ,
     {Five original blues} -
     (COUNTIF($A$1:$B$1,"blue")+COUNTIF($A$3:$B$3,"blue"))
   ,
     "Zero"
 )

Output with this is three, as expected.

This isn't a terrible solution for my 8 data points with two header rows, but I expect to have at least ten header rows and this does not scale very well.

I keep trying various things like

    - (if(isodd(row(A1:B4)) . . . 
    - countif(A1:B4,and(isodd(row(A1:B4)),find("blue",A1:B4) ...
    - arrayformula ...?

But haven't figured it out yet.

Thanks in advance!

Solution

  • This works for me, using the size of the array in the example above:

    countif(filter(A1:B4,isodd(row(A1:B4))),"~*BLUE")

    Broken down:

    1. Filter(A1:B4,isodd(row(A1:B4)))

    This produces just the odd lines of my array, which I'll call OddsOnly for ease of referencing it:

          A            B              C             D
  +---------------------------------------------------
1'| *Red         *Blue          Blue          Blue
3'| *Blue        Blue-xx        *Red          Red

    Then 

    2. countif(OddsOnly,"~*BLUE")

    I learned that * is a wild card character! So to escape the character, use a tilde. The above formula (2) counts all instances of the string *BLUE in the OddsOnly cells, which is sufficient to meet my needs.

    Note that I didn't need ARRAYFORMULA at all. The following two formulae produce identical results:

    without ARRAYFORMULA: countif(filter(A1:B4,isodd(row(A1:B4))),"~*BLUE")
with    ARRAYFORMULA: countif(arrayformula(filter(A1:B4,isodd(row(A1:B4)))),"~*BLUE")