In need of a flagging formula for 2 scenarios (that are tied together)

Hello All,

I've attempted to work on a formula with multiple variables but I cant seem to get a good way to count without having to include each specific fruit name. I have substituted my data with fruits and colors for simplicity. The goal would be to not be tied to only certain fruits, if 'vegetables' are included, it would continue to function the same way.

Scenario 1:
A new row is added to indicate the fruit that one chooses but for each row a counter accumulates for all entries that match. See Sheet 1 data in Column A and B.

Scenario 2:
Based on the fruit and the color, if a specific word, or in this case a specific colors do not match, it would be indicated as a FLAG. This would not include blanks. Using Sheet 1 and columns H,I,J, there are 2 apple entries with discrepant colors (One does not match the other), so its flagged. Whereas Kiwi, there are 3 entries, and only one indicates a color, so there is no FLAG.

I hope this is clear. Thank you all in advance,
B

I'm not sure what kind of result you expect to achieve.

The result would be for the word FLAG to display when there is a discrepancy amongst the same fruit having different colors. The goal would be that the list is ever expanding so having the word FLAG at a hypothetical row 200, would update each fruit with the same entry with FLAG.

Grape - Green
Grape - Green
... (200 rows down)
Grape - Purple

This last entry would add FLAG to all prior entries

Grape - Green FLAG
Grape - Green FLAG
...(200 rows down)
Grape - Purple FLAG

For Scenario 1, where you want to count the occurrences of each fruit regardless of the specific fruit name, you can use a combination of functions like COUNTIF and SUMPRODUCT. This formula will dynamically count the occurrences of each fruit without needing to specify each fruit name individually. Here's a formula you can use:

=SUMPRODUCT(--(A:A<>"")/COUNTIF(A:A,A:A&""))

This formula will count the unique occurrences of fruits in column A.

For Scenario 2, where you want to flag rows where there are discrepancies in colors for each fruit, you can use a combination of IF, COUNTIFS, and IFERROR. Here's a formula you can use:

=IF(COUNTIFS(A:A,A2,B:B,"<>"&B2)>0,"FLAG","")

This formula will check for each row if there are other rows with the same fruit (column A) but different color (column B), and if so, it will flag that row as "FLAG".

You can place these formulas in the respective columns (e.g., in column C for Scenario 1 and in column K for Scenario 2), and they will dynamically calculate the counts and flags based on the data in columns A and B (for Scenario 1) and columns H and I (for Scenario 2). Adjust the ranges as necessary based on your actual data range.