CODEWITHSUNDEEP
arrayscountgoogle-sheetsrowinstances
gidklio
gidklio

Reputation: 1

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

It needs to be NOT tripped up by:

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!

Upvotes: 0

Views: 2038

Answers (3)

sylenix
sylenix

Reputation: 161

I did it using a slightly different formula. Please check:

=COUNTIF(ARRAYFORMULA(IF(ISODD(ROW(A1:D4))=TRUE,A1:D4)),"Blue")

enter image description here

As per your rules, it avoided the asterisked & suffixed "Blue" values.

Upvotes: 1

gidklio
gidklio

Reputation: 1

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")

Upvotes: 0

JPV
JPV

Reputation: 27262

To count the occurrences of 'Blue' without the asterisk, try

=SUM(ARRAYFORMULA(N(REGEXMATCH(SUBSTITUTE(FILTER(A1:D4, ISODD(ROW(A1:A4))),"*B",""), "Blue"))))

For even rows, try

=SUM(ARRAYFORMULA(N(REGEXMATCH(SUBSTITUTE(FILTER(B1:E4, ISEVEN(ROW(B1:B4))),"*B",""), "Blue"))))

Upvotes: 0

Related Questions