Formula Not Returning Value Needed

Copper Contributor

I have a spreadsheet to track how many reports each associate performs per month. At times 2 or more associates work on a report together but my formula will not count that report towards all associates listed. 

For example, 2 associates work on a report together, lets call them Bill and Bob. Bill's name is listed first in cell G3 and Bob is listed second in cell G4 so both Bill and Bob should get credit for that report but my current formula will only give credit to the first name listed which is Bill, if I switch the names and put Bob in cell G3 and Bill in cell G4 then only Bob gets credit. What formula can I use to give all associates credit for the report?

I am currently using the countif formula to count how many times an associate name appears in column G but again for some reason only the first name listed for each report gets credit for that report. 

5 Replies

@Kat_000 

What is the exact data layout, like this?

image.png

Yes, that is exactly how the layout is.

@Kat_000 

Thank you. And what is output? You say "how many reports each associate performs per month", if so you may count Bill by name, doesn't matter which report. Or you count how many associates per report or something else?

Oh let me correct myself. All names listed get credit for the report but if the report is late only the first name listed gets a late report counted against them. I need all associate names listed to have that late report counted against them if the report is late.

I am working with multiple tabs and columns. I have tabs for each month of the year and an overall summary tab. For the month tab column F is the report type, column G is the report owners, column L is the commit date for the report, column M is the actual date of completion and column P has a formula to tell me if the report was on time per the dates in column L and M, column P simply states "Yes" for on time and "No" for not on time. My summary tab has a list of associate names, a column for total reports for the month, total on time and total late. It is the total late formula I am struggling with. My current formula is=COUNTIFS('May 2022'!G:G,Summary!L3,'May 2022'!P:P, "No"). Not sure what I am doing wrong or why my formula only works for the first name listed in column G for each report. I will provide more information if needed, I have been struggling for days and am at a lost.

@Kat_000 

The easiest and most correct way is to keep source data without blanks, i.e. like this

image.png

How to fill down existing data is explained here Fill Down Blank Cells Until the Next Value in Excel (3 Easy Ways) - Trump Excel