Count Number of Rows With Matching Product

[who am i]

Hello!

How can I count essentially the number of rows with a particular value? In column A is the order number and in columns B:E are products in the order. The issue is that a customer may order the same product twice in the same order, but that need only be counted once.

On a separate sheet (same one here for visibility) will be a list of all applicable products. I need a formula showing the number of orders each product is present in. I filled in the formula column with expected values.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Order	Product 1	Product 2	Product 3	Product 4						Products	Number of Orders
2	1	Crow	Crow	Eagle	Sparrow						Crow	1
3	2	Dongle	Puck								Dongle	2
4	3	Dongle									Puck	1
5	4	Crow Blow									Eagle	1
6	5	Blow									Sparrow	1
7											Crow Blow	1
8											Blow	1
Sheet1

 

[Fluff]

How about

=SUM(SIGN(MMULT(--($B$2:$E$10=K2),TRANSPOSE(COLUMN($B$2:$E$2)^0))))

 

[who am i]

Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[who am i]

how would I add additional criteria to this count? Like if there was a date column in the orders, and I wanted just the sum of orders between 2 dates?

 

[Fluff]

Can you post some sample data.

 

[who am i]

absolutely and let me know if you want me to start a new post so that another solution may be awarded.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Order	Product 1	Product 2	Product 3	Product 4	Order Date								Products	Count	Count
2	1	Red	Red	Yellow		1/5/2025								Red	1	0
3	2	Blue	Blue			1/2/2025								Blue	1	0
4	3	Green				12/30/2024								Green	0	1
5	4	Yellow				12/27/2024								Yellow	1	1
6
7
8
9
10
11														Beginning Date	1/1/2025	1/1/2024
12														End Date	12/31/2025	12/31/2024
Sheet1

Cell Formulas
Range	Formula
O12:P12	O12	=EOMONTH(O11,11)

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G							N	O	P
1	Order	Product 1	Product 2	Product 3	Product 4	Order Date								Products	Count	Count
2	1	Red	Red	Yellow		05/01/2025								Red	1	0
3	2	Blue	Blue			02/01/2025								Blue	1	0
4	3	Green				30/12/2024								Green	0	1
5	4	Yellow				27/12/2024								Yellow	1	1
6
7
8
9
10
11														Beginning Date	01/01/2025	01/01/2024
12														End Date	31/12/2025	31/12/2024
Sheet6

Cell Formulas
Range	Formula
O2:P5	O2	=SUM(SIGN(MMULT(($B$2:$E$10=$N2)*($F$2:$F$10>=O$11)*($F$2:$F$10<=O$12),TRANSPOSE(COLUMN($B$2:$E$2)^0))))
O12:P12	O12	=EOMONTH(O11,11)

 

[who am i]

EXCELLENT!!!!

 

[Fluff]

You're welcome.