sumif in Excel, using R but getting too many results

[PeterHui]

Hi there,

I have an Excel table here

when I try to do it in R - it would give me a result, but it's not what I want

  stringsAsFactors = FALSE,
            Status = c("a", "b", "c", "d", "e"),
             count = c(1L, 1L, 1L, 1L, 1L),
          Status.1 = c("b", "b", "b", "e", NA),
          Status.2 = c("c", "c", "e", NA, NA)
  )
 
 Test %>% 
   group_by(Status.1 ) %>%
   mutate( totals = sum(count))

Here I don't want the Totals for a to appear as 3, it should be 0 because "a" appears in Status.1 zero times.

any help would be appreciated.

[technocrat]

suppressPackageStartupMessages({
  library(dplyr)
})

DF <-   data.frame(
Status = c("a", "b", "c", "d", "e"),
count = c(1L, 1L, 1L, 1L, 1L),
Status.1 = c("b", "b", "b", "e", NA),
Status.2 = c("c", "c", "e", NA, NA)
)

DF %>% 
  group_by(Status.1 ) %>% 
  summarize(totals = sum(count))
#> # A tibble: 3 x 2
#>   Status.1 totals
#> * <chr>     <int>
#> 1 b             3
#> 2 e             1
#> 3 <NA>          1

^{Created on 2021-01-15 by the reprex package (v0.3.0.9001)}

[PeterHui]

Thanks for this - really appreciate it, but for the dataset, I need to get it into the format that is on the table, so I can only use mutate to add a new column.

I am looking for this

so the other status have to display as well. I know I can do a join, but would much rather that it is just a new column.