Sumproduct to count multiple criteria

[Pauljj]

Afternoon all, I am counting within a range (F5 to BZ5 ) if the cells contain one of 52 different codes. Rather then creating a very long code with COUNTIFS I am certain that I can do this using SUMPRODUCT and creating those 52 codes in a named range but I can't remember how to do it. Anyone able to help please ?

 

[Radoslaw Poprawski]

can you provide 2 lines of ur spreadsheet as minisheet with result example?

if the cells contain one of 52 different codes

are you looking for just 1 code number that is unique within this list?
or
are you trying to get all codes but with no reapets?

there is filter and unique functions in Excel, so maybe that's what ur looking for?

 

[Pauljj]

Hi perhaps, I haven't explained well.

I have a named range, this range has 52 unique codes in it. The named range is called CODEA

Within that named range, codes exist such as 'GTET' 'TQJW' 'LPIW' etc etc

On a sheet called planner I have a list of staff in column A


So on A5 I will have an employee called Jack Torro, in cells F5 to BZ5 there maybe some codes, if the code in H5 = GTET and in BD5 = LPIW, then

in CA5 I want the count to equal 2

Hope that makes sense

Thanks

Paul

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you please post some sample data & expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Marcelo Branco]

See if this example helps

Pasta1
	A	B	C	D	E	F	G	H	I	J	K
1	Names	CODE1	CODE2	CODE3	CODE4	CODE5	Result				CODEA
2	Charles	zzz	GTET	xxx	LPIW	abc	2				GTET
3	Mike	LPIW	xxx	zzz		def	1				TQJW
4	Mary	xxx	GTET		TQJW	LPIW	3				LPIW
5	Richard	xxx	vvv	zzz	yyy	bcd	0
Plan4

Cell Formulas
Range	Formula
G2:G5	G2	=SUMPRODUCT(--ISNUMBER(MATCH(B2:F2,CODEA,0)))

Named Ranges
Name	Refers To	Cells
CODEA	=Plan4!$K$2:$K$4	G2:G5

M.

 

[Pauljj]

See if this example helps

Pasta1
	A	B	C	D	E	F	G	H	I	J	K
1	Names	CODE1	CODE2	CODE3	CODE4	CODE5	Result				CODEA
2	Charles	zzz	GTET	xxx	LPIW	abc	2				GTET
3	Mike	LPIW	xxx	zzz		def	1				TQJW
4	Mary	xxx	GTET		TQJW	LPIW	3				LPIW
5	Richard	xxx	vvv	zzz	yyy	bcd	0
Plan4

Cell Formulas
Range	Formula
G2:G5	G2	=SUMPRODUCT(--ISNUMBER(MATCH(B2:F2,CODEA,0)))

Named Ranges
Name	Refers To	Cells
CODEA	=Plan4!$K$2:$K$4	G2:G5

M.

Thank you