count if based on specific criteria

[nikhil0311]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	Technology	Power	Energy				ID	Segment 1	Segment 2	Segment 3	Segment 4	Segment 5
2	12345	5	9	4				12345	Technology	Power	Power	Power	Power
3	34567	7	5	2				12345	Technology	Technology	-	Technology	Technology
4	66789	3	12	4				12345	Power	Energy	Energy	Energy	Energy
5								12345	Power	-	Power	Power	Power
6								34567	Technology	Power	Technology	Technology	Technology
7								34567	Energy	Technology	-	Power	Power
8								34567	Energy	Technology	Technology	Power	Power
9								66789	Technology	-	Power	Power	Power
10								66789	Energy	Power	Technology	Power	Power
11								66789	Energy	Power	Technology	Power	Power
12								66789	Energy	Power	Energy	Power	Power
Sheet1

Column A have IDs and Column B,C & D have segments
H1:M12 is the raw data
B2:D4 = Count required of ID + Segment (for example if ID = 12345 Segment = Technology then total count for this combination from range H1:M13 is 5)
based on combination of ID & Segment, I want to count the ID & Segments from range H1:M13

please let me know if you have any questions!

 

[Misca]

Try =SUMPRODUCT(($H$2:$H$12=$A2)*($I$2:$M$12=B$6))

 

[nikhil0311]

thanks for your help and quick reply. This works like a charm.

I just did a small change instead of B$6 i changed it to B$1

Again, Thanks for your help