Excel Formula required for count of results from multiple column conditions

[bobbyexcel]

Need help on my below issue.. I've 3 sheets to go through multiple conditions to get the count if condition satisfies.

Here is the screenshot of the data and my requirements..

I need the results in Sheet1 based on the country of the manager.. I used the below Index formula but didn't get the results of it. Can someone help me on this.

=COUNT(INDEX('Sheet1'!$F:$F,MATCH(1,(B3='Sheet3'!$R:$R)*('Sheet3'!$Q:$Q='Sheet2'!$N:$N)*('Sheet1'!$F1='Sheet2'!$O:$O),0)))

 

[Eric W]

Maybe:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			All (Australia + US)			Australia			US					Manager	Cat		Manager	ANL Status	SHC Status	GDP Status
2		Status	ANL	SHC	GDP	ANL	SHC	GDP	ANL	SHC	GDP			John	Australia		John	Completed	Completed	Hold
3		Completed	2	3	0	2	1	0	0	2	0			David	US		David	In Progress	Completed	In Progress
4		In Progress	1	0	3	0	0	1	1	0	2			Uri	US		Uri	Hold	Completed	In Progress
5		Hold	1	1	1	0	1	1	1	0	0			Bosh	Australia		Bosh	Completed	Hold	In Progress
Sheet1

Cell Formulas
Range	Formula
C3:E5	C3	=F3+I3
F3:K5	F3	=LET(a,FILTER(Sheet2!$N$2:$N$5,Sheet2!$O$2:$O$5=F$1),MMULT(SEQUENCE(,ROWS(a),,0),COUNTIFS(Sheet3!$Q$2:$Q$5,a,OFFSET(Sheet3!$R$2:$R$5,0,{0,1,2}),$B3)))
Dynamic array formulas.

 

[bobbyexcel]

Maybe:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			All (Australia + US)			Australia			US					Manager	Cat		Manager	ANL Status	SHC Status	GDP Status
2		Status	ANL	SHC	GDP	ANL	SHC	GDP	ANL	SHC	GDP			John	Australia		John	Completed	Completed	Hold
3		Completed	2	3	0	2	1	0	0	2	0			David	US		David	In Progress	Completed	In Progress
4		In Progress	1	0	3	0	0	1	1	0	2			Uri	US		Uri	Hold	Completed	In Progress
5		Hold	1	1	1	0	1	1	1	0	0			Bosh	Australia		Bosh	Completed	Hold	In Progress
Sheet1

Cell Formulas
Range	Formula
C3:E5	C3	=F3+I3
F3:K5	F3	=LET(a,FILTER(Sheet2!$N$2:$N$5,Sheet2!$O$2:$O$5=F$1),MMULT(SEQUENCE(,ROWS(a),,0),COUNTIFS(Sheet3!$Q$2:$Q$5,a,OFFSET(Sheet3!$R$2:$R$5,0,{0,1,2}),$B3)))
Dynamic array formulas.

Thanks allot and this is what I'm expecting.. Thanks allot for your time on helping this. Thumbs up to you..

 

[bobbyexcel]

@Eric W Hi Eric, Need small info. How can I add one more condition if I need in the above formula ?? if you look into the attached screenshot, I want to add one more condition to Australia location. How should I achieve that ? Please help ..

 

[bobbyexcel]

I've added like this and seems it is working for me.. hope my approach is correct ??

=LET(a,FILTER(Sheet2!$N$2:$N$5,Sheet2!$O$2:$O$5=F$1),MMULT(SEQUENCE(,ROWS(a),,0),COUNTIFS(Sheet3!$Q$2:$Q$5,a,OFFSET(Sheet3!$R$2:$R$5,0,{0,1,2}),$B3,OFFSET(Sheet3!$T$2:$T$5,0,{0}),$G4)))

 

[Eric W]

The columns in your example don't seem to match up with your formula, but it looks reasonable. It looks like you're looking for the 255 or 1200. You don't really need to use OFFSET for the last condition though. Just use Sheet3!$T$2:$T$5 like the first condition.