Why is this one CountIfs expression getting a Value error?

[JenniferMurphy]

I was working on a workbook to keep track of my ratings of various items I purchase. I ran into a curious problem in the snack bar sheet that I cannot figure out.

I have 2 tables. The Product table (TblProdSB) is a list of the products (snack bars in this sheet). The Log table (TblLogSB) is a list of the ratings each time I try one of the snack bars. I have simplified the tables. In all my other sheets with similar tables, if I add a new product to the Product table, the count will show zero. For some reason, this one gets a Value error. I am going to include 3 minisheets to show how this error disappears and then reappears.

Minisheet #1: Value error in D8

CountIf.xlsx
	B	C	D	E	F	G	H	I	J
3		Product Table						Usage Log Table
4	Brand	Flavor	# Tried		Date	OK?	Brand	Flavor	Rating
5	Kind	Dark Chocolate Nuts & Sea Salt	1		5/30/24	Ok	Kind	Dark Chocolate Nuts & Sea Salt	82
6	Kind	Dark Chocolate Cherry Cashew	1		5/30/24	Ok	Kind	Dark Chocolate Cherry Cashew	88
7	Kind	Breakfast Bars	1		5/29/21	Ok	Kind	Breakfast Bars	40
8	Kind	Dark Chocolate Mint	#VALUE!
Snack Bars

Cell Formulas
Range	Formula
G5:G7	G5	=IF(COUNTIFS(TblProdSB[Brand],[@Brand],TblProdSB[Flavor],[@Flavor])=1,"Ok","XXX")
D5:D8	D5	=COUNTIFS(TblLogSB[@Brand],[@Brand],TblLogSB[@Flavor],[@Flavor])

Minisheet #2: If I add a row to the Log table (on the right), the error disappears

CountIf.xlsx
	B	C	D	E	F	G	H	I	J
3		Product Table						Usage Log Table
4	Brand	Flavor	# Tried		Date	OK?	Brand	Flavor	Rating
5	Kind	Dark Chocolate Nuts & Sea Salt	1		5/30/24	Ok	Kind	Dark Chocolate Nuts & Sea Salt	82
6	Kind	Dark Chocolate Cherry Cashew	1		5/30/24	Ok	Kind	Dark Chocolate Cherry Cashew	88
7	Kind	Breakfast Bars	1		5/29/21	Ok	Kind	Breakfast Bars	40
8	Kind	Dark Chocolate Mint	0			XXX
Snack Bars

Cell Formulas
Range	Formula
D5:D8	D5	=COUNTIFS(TblLogSB[@Brand],[@Brand],TblLogSB[@Flavor],[@Flavor])
G5:G8	G5	=IF(COUNTIFS(TblProdSB[Brand],[@Brand],TblProdSB[Flavor],[@Flavor])=1,"Ok","XXX")

Minisheet #3: If I add a new row to the Product table (on the left), the error reappears.

CountIf.xlsx
	B	C	D	E	F	G	H	I	J
3		Product Table						Usage Log Table
4	Brand	Flavor	# Tried		Date	OK?	Brand	Flavor	Rating
5	Kind	Dark Chocolate Nuts & Sea Salt	1		5/30/24	Ok	Kind	Dark Chocolate Nuts & Sea Salt	82
6	Kind	Dark Chocolate Cherry Cashew	1		5/30/24	Ok	Kind	Dark Chocolate Cherry Cashew	88
7	Kind	Breakfast Bars	1		5/29/21	Ok	Kind	Breakfast Bars	40
8	Kind	Dark Chocolate Mint	0			XXX
9			#VALUE!
Snack Bars

Cell Formulas
Range	Formula
G5:G8	G5	=IF(COUNTIFS(TblProdSB[Brand],[@Brand],TblProdSB[Flavor],[@Flavor])=1,"Ok","XXX")
D5:D9	D5	=COUNTIFS(TblLogSB[@Brand],[@Brand],TblLogSB[@Flavor],[@Flavor])

What is going on here?

 

[Fluff]

The formula in col D is wrong, it should be like the countifs in col G

 

[JenniferMurphy]

Ok, I see it now. The expression

=COUNTIFS(TblLogSB[@Brand],[@Brand],TblLogSB[@Flavor],[@Flavor])

has an "@" symbol in both criteria parameters, which means it is only testing one row, not the entire column. The correct expression should have been

=COUNTIFS(TblLogSB[Brand],[@Brand],TblLogSB[Flavor],[@Flavor])

It now works.

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JenniferMurphy]

In case anyone is interested, this example shows two Product tables. The one on the left has the incorrect CountIfs expression. The one in the middle has the correct expression. I was confused by the seemingly correct counts in the # Tried column. But that was just an unfortunate accident in that the values in the Brand and Flavor columns were in the same rows.

CountIf.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
3		Incorrect Product Table (TblProdSBErr)				Correct Product Table (TblProdSBOk)						Usage Log Table (TblLogSB)
4	Brand	Flavor	# Tried		Brand	Flavor	# Tried		Date	OK?	Brand	Flavor	Rating
5	Kind	Dark Chocolate Nuts & Sea Salt	1		Kind	Dark Chocolate Nuts & Sea Salt	1		5/30/24	Ok	Kind	Dark Chocolate Nuts & Sea Salt	91
6	Kind	Dark Chocolate Cherry Cashew	1		Kind	Dark Chocolate Cherry Cashew	1		5/30/24	Ok	Kind	Dark Chocolate Cherry Cashew	82
7	Kind	Breakfast Bars	1		Kind	Breakfast Bars	1		5/30/24	Ok	Kind	Breakfast Bars	88
8	Kind	Dark Chocolate Mint	1		Kind	Dark Chocolate Mint	1		5/30/24	Ok	Kind	Dark Chocolate Mint	85
9			#VALUE!				0
Snack Bars

Cell Formulas
Range	Formula
K5:K8	K5	=IF(COUNTIFS(TblProdSBOk[Brand],[@Brand],TblProdSBOk[Flavor],[@Flavor])=1,"Ok","XXX")
D5:D9	D5	=COUNTIFS(TblLogSB[@Brand],[@Brand],TblLogSB[@Flavor],[@Flavor])
H5:H9	H5	=COUNTIFS(TblLogSB[Brand],[@Brand],TblLogSB[Flavor],[@Flavor])