Merge two tables and aggregate

Convert name dataframe to long format using stack. Then merge as usual, then aggregate on Gender and Class:

aggregate(Grades ~ Class + ind,
          merge(grades, na.omit(stack(name)), by.x = "Name", by.y = "values"), sum)
#   Class    ind Grades
# 1     A   Male      5
# 2     B   Male      5
# 3     A Female     10
# 4     B Female      5
# 5     C Female      5

#data
name <- read.table(text = "
Male Female
Sam Tina
NA Anna", header = TRUE)

grades <- read.table(text = "
Class  Name  Grades
A      Sam   5
B      Sam   5
A      Tina  5
B      Tina  5
C      Tina  5
A      Anna  5", header = TRUE)

A solution with no dependencies looks like this::

Your data might look like this:

names <- data.frame(gender = c("Male", "Female", "Female"),
                    name = c("Sam", "Tina", "Anna"))

grades <- data.frame(class = c("A", "B", "A", "B", "C", "A"),
                     name =  c("Sam", "Sam", "Tina", "Tina", "Tina", "Anna"),
                     grades = c(5, 5, 5, 5, 5, 5))

Then you can match the gender variable into the grades data.frame and summarize the grades by class and gender:

## Match the gender variable into your dataframe with grades
grades$gender <- names$gender[match(grades$name, names$name)]

## summarize grades by class and gender
result <- tapply(grades$grades, list(grades$class, grades$gender), sum)

Finally you will want to reshape to long and cleanup the data.frame a little:

## reshape to long and cleanup
result <- as.data.frame(result)
result$class <- rownames(result)
result <- reshape(result,
                  direction = "long",
                  varying = list(1:2),
                  timevar = "gender",
                  v.names = "grades")
result <- result[, c("class", "gender", "grades")]

You should get this:

> result
class gender grades
1.1     A      1     10
2.1     B      1      5
3.1     C      1      5
1.2     A      2      5
2.2     B      2      5
3.2     C      2     NA

Does this work:

library(dplyr)
library(tidyr)
name %>% pivot_longer(cols = everything(), names_to = 'Gender') %>% distinct() %>% 
   inner_join(grades, by = c('value' = 'Name')) %>% select(Class, Gender, Grades) %>% 
   group_by(Class, Gender) %>% summarise(Grades = sum(Grades))
`summarise()` regrouping output by 'Class' (override with `.groups` argument)
# A tibble: 5 x 3
# Groups:   Class [3]
  Class Gender Grades
  <chr> <chr>   <dbl>
1 A     Female     10
2 A     Male        5
3 B     Female      5
4 B     Male        5
5 C     Female      5

Data used:

name
  Male Female
1  Sam   Tina
2  Sam   Anna
grades
# A tibble: 6 x 3
  Class Name  Grades
  <chr> <chr>  <dbl>
1 A     Sam        5
2 B     Sam        5
3 A     Tina       5
4 B     Tina       5
5 C     Tina       5
6 A     Anna       5