Edit: Reworded, provided table & examples and a better explanation.

I am a novice at excel; I've made a few formulas, but this one has really stumped me. I'm unable to search for each value individually because they change day to day and it would require too much upkeep to re-write the formula every day with each unique ID - when one is completed, it will not be on the list the next day.

To explain: Each ID in column B can appear multiple times, and can have different statuses associated with it - (COMPLETE, IN PROGRESS, etc.) I need cell C2 to display the total amount of IDs marked "COMPLETE", ignoring duplicate values, and only being counted if every instance of the ID is marked "COMPLETE". So if the ID 48324 appears 5 times, I only want the counter in C2 to register it if all 5 are marked "COMPLETE", and then treat the entire 5 "COMPLETE"s as 1 value for the count. So all 5 "COMPLETE"s would only increase the total "COMPLETE" count by 1.

I've tried vlookup and countif, but I'm fairly out of my depth and I need advice for how best to proceed. Thanks!

CodePudding user response：

Use COUNTIFS to get the data and SUMPRODUCT to sum the results.

=SUMPRODUCT((COUNTIFS(A:A,"COMPLETE",B:B,B2:B21)=COUNTIFS(B:B,B2:B21))/COUNTIFS(B:B,B2:B21))

(COUNTIFS(A:A,"COMPLETE",B:B,B2:B21)=COUNTIFS(B:B,B2:B21)) returns 1 or 0 depending on if the counts match, meaning all are marked "complete" for each ID or Not.

Then we divide that by the number of times the ID is in the list so the whole adds to 1.

Then we sum.