How do I write an excel formula to count the number of times a result occurs in a column for each user (user name in another column of same data set)?

Rosie, Nina 20 Reputation points
2024-06-24T18:16:05.14+00:00

Data Sources:

  • Summary data table which contains a column for employee name and a column for each type of occurrences we want to track, e.g. wrong status or past due date
  • Detail data is on another tab and contains a column with employee name as well as columns for each type of occurrence we want to track

How do I say...If employee is John Doe (column B3 of summary table), count occurrences of "1" in Past Due Date column in table on another tab?

I attempted a pivot table but when I drop the fields in the values and select "count", I get the same result for every one. I attempted to add to data model for distinct count and it just creates a data model but I'm still not able to get the right result in a pivot table.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
0 comments No comments
{count} votes

Accepted answer
  1. Barry Schwarz 2,511 Reputation points
    2024-06-24T21:40:16.82+00:00

    You use the COUNTIFS function. For example, let sheet1 contains names in B1:B10, past due summary data in E1:E10, and other summary data in F1:F10, G1:G10, etc. Let sheet2 contain names A1:A100, past due data in L1:L100, and other data in other columns.

    In E1 on sheet1 you would enter the formula

    =COUNTIFS(sheet2!A$1:A$100,B1,sheet2!L$1:L$100,1)
    

    and copy this formula to E2:E10. Cell E3 would then contain the information for John Doe.

    Be aware that this would not count an entry in column L that contained a 2. If you wanted to count this entry also, then you change the final argument from 1 to ">"&0 or ">="&1 (quotes required). If you meant to count this entry twice, then you are summing and not counting and would use the similar SUMIFS function


0 additional answers

Sort by: Most helpful