CODEWITHSUNDEEP
google-sheets
papacom
papacom

Reputation: 1

ArrayFormula + Countifs

Sheet 1

        A           B         C . . .
1 STUDENT NAME   LEC. ID    GRADE
2 JOHN            123abc      A
3 SARAH           345def      B+
4 RAHUL           123abc      B+
.
.
.

Sheet 2

   A            B      C   D   E   F  G   H   I  J   K   L   M . . .
1 LEC. NAME  LEC. ID   A   B+  B-  B  B-  C+  C  C-  D   E   F
2 MARK       123abc    1   1
3 JEAN       123def        1
.
.
.

I need to count based on LEC. ID and GRADE in Sheet 1 and display it in Sheet 2. Is anyone know how to use ArrayFormula and countifs to do this.

I need to insert the formula at C1, D1, . . . in Sheet 2.

Thank you.

Upvotes: 0

Views: 154

Answers (3)

Frederic Bazin
Frederic Bazin

Reputation: 1529

You can solve it with just a pivot table:

column= grade rows=lec. name, lec. id value= counta(grade)

don't forget to select checkbox repeat row value for 1st row

Upvotes: 0

TheMaster
TheMaster

Reputation: 50774

Sheet3!B1:

=QUERY(Sheet1!B:C,"Select B,count(B) group by B pivot C")

Add the LEC.Name later.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

A simple COUNTIFS() will do it:

=COUNTIFS(Sheet1!$B:$B,$B2,Sheet1!$C:$C,C$1)

Upvotes: 1

Related Questions