2
=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.

player0
  • 124,011
  • 12
  • 67
  • 124
aiorr
  • 547
  • 4
  • 11

2 Answers2

3

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you. Is there a way to write it so that the number appears on the cell with the formula, instead of the cell to the right? Edit: I changed it into "= countif(FILTER(E6:E, MOD(ROW(E6:E)-ROW(E6), 7)=0),"orange")" and it works as I desired. Thank you! – aiorr Apr 18 '20 at 17:12
0

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

Sapien
  • 1