How do I sum up specific things in Numbers?
You are correct in that you need to use the SUMIF function.
This is the specification from the Function Browser:

SUMIF(test-values, condition, sum-values)


Try this in your cell:

=SUMIF(CATEGORY; "=HW"; POINTS)