CODEWITHSUNDEEP
google-sheetsfiltergoogle-sheets-formulacountinggoogle-sheets-query
aiorr
aiorr

Reputation: 589

Counting the number of occurrences in a cell every nth row in Google Sheet

=countif('Sheet2'!$K$6,$AD25)+countif('Sheet2'!$K$13,$AD25)+countif('Sheet2'!$K$20,$AD25)
  +countif('Sheet2'!$K$27,$AD25)+countif('Sheet2'!$K$34,$AD25)+countif('Sheet2'!$K$41,$AD25)
  +countif('Sheet2'!$K$48,$AD25)+countif('Sheet2'!$K$55,$AD25)

This is what I have so far, and I am wondering if there is a more eloquent way to approach this.

  1. In Sheet2, every 7 rows, starting at 6 (i.e. 6,13,20,27,34,41,...) at column K, there is a dropdown with list of items e.g. {apple, orange, banana}.
  2. $AD25 refers to orange.
  3. In another sheet, I've used the formula above to count the number of times "orange" was selected in the dropdown list. If orange was selected in $K$6 and $K$34, the formula would return 2.

Every week, we fill it out one by one, but since this is ongoing process, after we complete row 55, I would need to add +countif('Sheet2'!$K$62,$AD25) to the formula, which isn't really efficient.

Is there more efficient way? What I would imagine is something along of 

countif('Sheet2'!$K$mod(???, 7)=0,$AD25)

but can't really figure it out.

Upvotes: 2

Views: 881

Answers (2)

Sapien
Sapien

Reputation: 1

A Simple solution would be to Use the UNIQUE() and COUNTIF() formulas

  • In D2 UNIQUE(A2:A)
  • In E2 ARRAYFORMULA(COUNTIF(A:A,D2:D10))

I'm using ARRAYFORMULA() to propagate the COUNTIF() formula to the rest of the range.

See Example

Upvotes: 0

player0
player0

Reputation: 1

like this:

=QUERY(FILTER(A6:A, MOD(ROW(A6:A)-ROW(A6), 7)=0), 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''", 0)

enter image description here

Upvotes: 3

Related Questions