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)?

Question

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.

Answer 1

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