Tried different approaches, but the best I could came with is:
=IF($C$2:$C$65365=B4, COUNTA($G$2:$G$65365))
but it returns #SPILL
| B | C | D | E | F | G | |
|---|---|---|---|---|---|---|
| 10/26/2022 | The Quarry | Hunter | 1:39 | Chest | The Dragon's Shadow | |
| 10/26/2022 | The Quarry | Hunter | 1:57 | Chest | ||
| 10/30/2022 | Perdition | Titan | 3:30 | Chest | Actium War Rig | |
| 10/30/2022 | Perdition | Titan | 3:06 | Chest |
CodePudding user response：
Use 
• Formula used in cell D7
=COUNTIFS(C2:C5,C7,G2:G5,"<>")
Using 
• Formula used in cell E7
=SUMPRODUCT(--(C2:C5=C7)*(G2:G5<>""))
Using some fancy functions to simplify, clarify and improved performance. 
• Formula used in cell F7
=LET(criteria_range1,C2:C5,criteria1,C7,
criteria_range2,G2:G5,criteria2,"<>",
COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2))