Count only One Time values in Column B if Column C <>String

[drom]

Hi and Thanks in advance
I am using Excel 2010 for this job.
I would like a Formula to count How many times a Value in Columns B, (Header AAA) and Column C (Header CCC) <> FISH do we have but:

Row 10 and 11 should be 1 (NOT 2)​

Row 18 and 19 should be 1 (NOT 2)​

Same for Row 22 and 23​

If I do this manually I will Get 6 (D2), (Check Yellow Cells)
I would lke to have a formula in B4 (Does not matter)
For This Query I am not using a Table, but I do not care if a have to use a Table if necessary

Column C (Header Manually) and Column E (Header AAA& BBB) is just to make it easier to understand my need

 

[jasonb75]

Try this formula which must be array confirmed with Ctrl Shift Enter, not just Enter.

=SUM(IFERROR(1/COUNTIFS(A7:A25,A7:A25,B7:B25,B7:B25,B7:B25,C2),0))

Note that the formula is untested, if it doesn't work then a usable sample will be needed (one that can be copied to excel without retyping everything). The best way to post one is with XL2BB (see link below).

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com