CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
EvangeliusAg
EvangeliusAg

Reputation: 159

Countif True/False values for alternating rows

How can I automate my scoring formula to count odd rows as 1 for TRUE and even rows as 1 for FALSE?

Backstory

I'm trying to import a quiz that I found in the back of an ancient, eldritch tome into G Sheets. The order of the questions is fixed - the notes in the margins are very specific that a "dire fate" awaits anyone who "dares disturb these ancient mysteries." So I'm putting the questions in G Sheets in order, but in order to count the scores, I need to have every odd row give +1 if the answer is TRUE and every even row give +1 if the answer is FALSE.

Row Number Question Answer Score
1 Dread Cthulhu is my personal Lord and Slayer TRUE 1
2 Men are destined to master their own fates FALSE 1
3 This way is madness TRUE 0
4 These secrets should have stayed buried FALSE 0

I know I could brute force this with addition, such as

=COUNTIF(C2,TRUE)+COUNTIF(C4,TRUE)+COUNTIF(C6,TRUE)...

but every minute I spend typing, I feel the tendrils of existential dread gnawing at the foundations of my soul. Plus, that sounds super-boring.

So, is there a way to automate having COUNTIF() (or COUNTIFS()) do this for me?

Things That I Have Tried or Thought About

=COUNTIFS(C2:C666,TRUE,A2:A666,ISEVEN(ROW)

Did try this, but it's just giving me the total number of true values:

=ARRAYFORMULA(COUNTIFS(A3:A24,ISEVEN(ROW()),A3:A24,TRUE))

What else y'all got?

Upvotes: 1

Views: 508

Answers (2)

MattKing
MattKing

Reputation: 7783

=ARRAYFORMULA(ABS(C3:C-ISEVEN(ROW(C3:C))))

UPDATE: To get the total:

=SUMPRODUCT(ABS(C3:C-ISEVEN(ROW(C3:C))))

Upvotes: 1

doubleunary
doubleunary

Reputation: 18978

Matt's formula looks like the way to go. Try this to get the total:

=arrayformula( sum( islogical(C3:C) * abs(C3:C - iseven(row(C3:C))) ) )

Upvotes: 1

Related Questions