How to use countif based on multiple vlookup results

1
votes

In Google Sheet, I am trying to count the number of responses received on several criteria.

  1. All responses are tied to a specific event ID in Column A in sheet 1.

  2. There are multiple respondents per event ID in sheet 2 in Column B. Each respondent has their own row. It shows their event ID in Column A.

  3. Their response is on sheet 2 column C.

I am trying to figure out how many responses occurred per event. I have a feeling this countif formula might need a vlookup or match/index nested inside but I'm not sure how to construct it. Any help would be very appreciated.

Example Sheet 1

Example Sheet 2

2
excelgoogle-sheetsexcel-formulavlookupcountif
Can you provide sample data and expected output? – EDS

2 Answers

1
votes

try:

=QUERY(Sheet2!A:C; "select A,count(B),count(C) where A is not null group by A"; 1)
0
votes
B1 Formula - COUNTIF($F$2:$F$9,A2)
C1 Formula - COUNTIFS($H$2:$H$9,"<>"&"",$F$2:$F$9,A2)

enter image description here