Bug found in table referencing.

[JenniferMurphy]

I found what I think can only be a(nother) bug in Excel's table referencing. I have several sheets that I use to keep track of products that I buy. Each sheet has 2 tables. In the sheet for rating juices, there is a Product table (TblJuiceProd) with one record for each product, and a Log table (TblJuiceLog) with one record for each time I try a product so I can give it a rating. Both tables have two matching columns, Brand and Flavor. The Product table has a Conditional Formatting rule on the Brand+Flavor columns to check for duplicates.

The Log table has an OK? column, which is where the bug raised its ugly head. It has this expression to check that each entry has a match in the Product table.

=IF(COUNTIFS(TblJuiceProd[@Brand],[@Brand],TblJuiceProd[Flavor],[@Flavor])=1,"Ok","Error")

Prior to today, the Product table only had one record. When I added a new row, the expression above got a Value error. After a lot of head scratching, I noticed that if I select the cell with the error, both columns in the flavor column of the Product table are selected, but only the first cell on the Brand column.

After some fiddling, I discovered that if I delete everything but this: =COUNTIFS(TblJuiceProd[@Brand],[@Brand]), it works. but if I put it together, it fails. Eventually, I went back to the full expression, deleted the two Brand parameters, then reentered them, and it now works.

It's a puzzle why the two flavor parameters work, but the two Brand parameters do not.

Anyway, here is a section of the Product table.

Product comparison, Food.xlsx
	B	C	D
3	Brand	Flavor	Vendor
4	North Coast	Apple Cider, Organic	Amazon Fresh
5
Juice

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:C5,O4:P4	Expression	=COUNTIFS($B$4:$B$5,$B4,$C$4:$C$5,$C4)>1	text	NO

And here is a section of the Log table with the erroneous error.

Product comparison, Food.xlsx
	M	N	O	P	Q
3	Date	OK?	Brand	Flavor	Rtg
4	11/25/24	#VALUE!	North Coast	Apple Cider, Organic	93
Juice

Cell Formulas
Range	Formula
N4	N4	=COUNTIFS(TblJuiceProd[@Brand],[@Brand],TblJuiceProd[Flavor],[@Flavor])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:C5,O4:P4	Expression	=COUNTIFS($B$4:$B$5,$B4,$C$4:$C$5,$C4)>1	text	NO

 

[StephenCrump]

Based on just a very quick look ....

Isn't the problem the @ highlighted, which should be removed?

=IF(COUNTIFS(TblJuiceProd[@Brand],[@Brand],TblJuiceProd[Flavor],[@Flavor])=1,"Ok","Error")

With one row, the formula will work because criteria_range1 and criteria_range2 are the same size. But for >1 row, you'll get a #VALUE! error.

 

[JenniferMurphy]

Well. Either your quick look is brilliant or my hour of head scratching is clueless. Yes, that is exactly the problem. Thank you.

I guess I owe the Excel developers an apology.