Countifs Function for Criteria 1 in Column Criteria 2 in Row

[nawdrizzy]

Hi,

I'm hoping that there's a way to do this.

I'm trying to do a countifs formula where one of the criteria is in the column and from that column the data that I want to count is in that row.

So basically something like Countif(A:A,name,D1:AI,"LE"). I've attached an image of the sheet that I'm working on. Any help will be greatly appreciated

 

[Tricepsratops]

I dont understand a thing, since there is no Rows Column in the picture, a Result example of what you need would help.

Try this? =COUNTIF(B3:D3,B1)

I dont know if I really understood.

Traductor de Formulas - copia - copia.xlsm
	A	B	C	D	E	F
1		Criteria1	Criteria2
2	Laura1
3	Laura2	Criteria1	Criteria2	Criteria1	2	Counts Criteria1 forLaura2
4
5	Laura3		Criteria1
6	Laura5	Criteria1	Criteria1	Criteria2	1	Counts Criteria2 forLaura5
7
8	Laura4
Team 2

Cell Formulas
Range	Formula
E3	E3	=COUNTIF(B3:D3,B1)
E6	E6	=COUNTIF(B6:D6,C1)

 

[nawdrizzy]

So i'm trying to take the value "Parts-17000.1" and add it to a second sheet then I want to run a count formula and count how many "LE" there is in row 4 for "Parts-17000.1". I've attached a new image to help clarify

 

[Tricepsratops]

So you want to have in Sheet2 2 columns,
Column A: Employee name
Column B: the LE count, a number.

Am I right?

 

[nawdrizzy]

Yes that's correct

 

[Tricepsratops]

Then its simple create a second worksheet, in column A copy the names, in column B use the formula I gave you before.

Example for the first name, "A4": (note that 'sheet1' is an example a placeholder, you have to actually put your sheet name in its place)
Put this in B4 in Sheet2 =COUNTIF('sheet1'!$D4:$AI4,'sheet1'!$AA$1)

then drag it down until B30 or whatever namy names you have.

If it doesnt work try this =COUNTIF('sheet1'!$D4:$AI4,"LE")

Let me know how it goes.

 

[nawdrizzy]

The second formula would work if I only had one First name. Since I have multiple names I want have a dynamic formula that can count the "LE" for each name. The image i uploaded shows D1 as the name. I want to have E3 to count all the "LE" in D4:AI4 in the date sheet.

 

[Tricepsratops]

Ok, so assuming your picture in message #1 is sheet1, and your picture in message #7 is sheet2.

This code looks the name of D1 sheet2 (Name4), in Sheet1 and obtains the possition of the row of that name.
Then it counts how many times "LE" appears in that row.

Look at this Example.
Sheet1

Traductor de Formulas - copia - copia.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1
2
3
4	Name1							LE
5	Name2											LE		LE				LE
6	Name3									LE											LE
7	Name4								LE					LE		LE		LE
8	Name5
9	Name6
10	Name7
11	Name8
12
sheet1

Sheet2

Traductor de Formulas - copia - copia.xlsm
	B	C	D	E	F
1			Name4
2
3			4	<----- counts the number of "LE" in D1 (Name4)
4
sheetcount

Cell Formulas
Range	Formula
D3	D3	=COUNTIF(OFFSET(sheet1!D1,MATCH(D1,sheet1!A1:A11,)-1,0,1,31),"LE")

Summing up, this is the formula you need to use in your D3 cell in your sheet2, =COUNTIF(OFFSET(sheet1!D1,MATCH(D1,sheet1!A1:A11,)-1,0,1,31),"LE")
Things you have to modify in the formula: highlited in bold.

• sheet1 for your actual sheet1 name.
• "LE" if you want to count other thing than "LE", or make it dynamic having the "LE" text in other cell.
• 11 , my sample data only had 8 names, from A1 to A11, yours should be bigger so change that 11 for a bigger number that includes all your name rows, for example 30.

ALSO, you should enter the formula with CRTL SHIFT ENTER to avoid problems.

Let me know if you have any trouble.

 

[nawdrizzy]

This works perfectly!! Thank you so much, I really appreciate it.

 

[Tricepsratops]

I'm glad we have been able to solve it.