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