Advanced SUMPRODUCT

[Gabriel222]

Hello everyone !

I have an issue with the following and I was wondering whether anyone knows the solution ?

Goal : to recreate the below value of 3 in cell C6 using only formulas (no VBA or PowerQuery allowed),
the formula in C6 is saying how many Men with the QPV attribute from Sheet2 have "Values" between 5 and 7 (inclusive)
the value for a given person is the amount of "Noticed problem" and "No issues" that he or she has in sheet3

Constraints: the ONLY thing allowed is to add formulas in the cell C6, I cannot add to (or modify) in ANY way the other sheets, or the other cells of the current sheet

So far : I have managed to get the desired result, but I have had to create column D in sheet 2, which is forbidden.
I would love to calculate and evaluate the array in sheet 2 D2:D13 , directly within the formula in sheet 1 C6, but I can't find out how.

Thank you for your help !

Sheet1

Advanced Sumproduct ENG.xlsx
	A	B	C	D
1	Values = "Noticed problem" + "Being resolved" by line
2	Indicator	Men
3		Total	of which QPV
4	<2
5	2 to 4
6	5 to 7		3
7	8 +
Sheet1

Cell Formulas
Range	Formula
C6	C6	=SUMPRODUCT(--(Sheet2!$D$2:$D$13<=7)*(Sheet2!$D$2:$D$13>=5)*--(Sheet2!$B$2:$B$13="Male")*--(Sheet2!$C$2:$C$13="QPV"))

Sheet 2

Advanced Sumproduct ENG.xlsx
	A	B	C	D
1	Name	Gender	Zone	Values = "Noticed problem" + "Being resolved" by line
2	Aurélie	Female	QPV	4
3	Claude	Male	Ville	7
4	Jean	Male	Ville	4
5	Justine	Female	Ville	6
6	Laurent	Male	QPV	6
7	Luc	Male	QPV	7
8	Marie	Female	QPV	3
9	Nicolas	Male	Ville	5
10	Paul	Male	Ville	5
11	Pierre	Male	Ville	8
12	Sofiane	Male	QPV	5
13	Théo	Male	Ville	7
Sheet2

Cell Formulas
Range	Formula
D2:D13	D2	=SUMPRODUCT(--(Sheet3!2:2="Noticed problem")--(Sheet3!2:2="Being resolved"))

Sheet 3

Advanced Sumproduct ENG.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Name	Family Status	Justice Status	Lodging Status	Economical Status	Employed Status	Admin Status	City Status	Knowledge Status	Other Status
2	Aurélie	No issues	Noticed problem	No issues	Noticed problem	No issues	Noticed problem	No issues	No issues	Noticed problem
3	Claude	Noticed problem	Noticed problem	Being resolved	Noticed problem	Being resolved	Noticed problem	Being resolved	No issues	No issues
4	Jean	No issues	Being resolved	Being resolved	Noticed problem	No issues	No issues	No issues	No issues	Being resolved
5	Justine	No issues	Noticed problem	Noticed problem	Noticed problem	No issues	Being resolved	Being resolved	No issues	Noticed problem
6	Laurent	No issues	No issues	Being resolved	Being resolved	No issues	Being resolved	Being resolved	Noticed problem	Being resolved
7	Luc	Noticed problem	Noticed problem	Noticed problem	Noticed problem	No issues	Being resolved	No issues	Noticed problem	Being resolved
8	Marie	No issues	No issues	No issues	No issues	Noticed problem	No issues	Noticed problem	Being resolved	No issues
9	Nicolas	Noticed problem	Noticed problem	No issues	Noticed problem	Being resolved	No issues	Noticed problem	No issues	No issues
10	Paul	Being resolved	Being resolved	No issues	No issues	Being resolved	Noticed problem	No issues	No issues	Being resolved
11	Pierre	Noticed problem	No issues	Noticed problem	Noticed problem	Noticed problem	Noticed problem	Noticed problem	Noticed problem	Noticed problem
12	Sofiane	No issues	No issues	Being resolved	No issues	No issues	Being resolved	Being resolved	Noticed problem	Being resolved
13	Théo	Being resolved	Being resolved	Noticed problem	Noticed problem	Noticed problem	Noticed problem	No issues	No issues	Being resolved
14		Noticed problem	No issues	No issues	No issues	No issues	No issues	No issues	No issues	No issues
Sheet3

 

[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’)

 

[Gabriel222]

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’)

Thank you for your reply, I've updated my account details now.

I am on Windows and am using Excel 365, although this particular solution needs to work on Excel 2016.

 

[Fluff]

this particular solution needs to work on Excel 2016.

Ouch.
With 365 you could have used

=LET(f,FILTER(Sheet3!B2:J14,ISNUMBER(MATCH(Sheet3!A2:A14,FILTER(Sheet2!A2:A13,(Sheet2!B2:B13="Male")*(Sheet2!C2:C13="QPV")),0))),m,MMULT((f="noticed problem")+(f="being resolved"),SEQUENCE(COLUMNS(f),,,0)),SUM((m>=5)*(m<=7)))

I'll see if I can do-it without Let or Filter, but it won't be pretty & no guarantee it will work for 2016.

 

[Fluff]

Will the names on sheets 2 & 3 always be in the same order?

 

[Gabriel222]

Will the names on sheets 2 & 3 always be in the same order?

Yes they will !

 

[Fluff]

Ok, in that case you could try

=SUMPRODUCT((MMULT((Sheet2!B2:B13="male")*(Sheet2!C2:C13="Qpv")*((Sheet3!B2:J13="noticed problem")+(Sheet3!B2:J13="being resolved")),TRANSPOSE(COLUMN(Sheet3!B2:J2)^0))>=5)*(MMULT((Sheet2!B2:B13="male")*(Sheet2!C2:C13="Qpv")*((Sheet3!B2:J13="noticed problem")+(Sheet3!B2:J13="being resolved")),TRANSPOSE(COLUMN(Sheet3!B2:J2)^0))<=7))

 

[Gabriel222]

Wow! Thank you so much Fluff !!
it works on my Excel 365

I am going to check now to see if it works on Excel 2016 (it most likely will, I don't see why it wouldn't)
and then I am going to try and understand the formula (I am mainly not accustomed to using MMULT)

Thanks a ton !

 

[Fluff]

I am going to check now to see if it works on Excel 2016

?

 

[Eric W]

Another possibility:

=SUMPRODUCT(ISNUMBER(MATCH(MMULT((--ISNUMBER((MATCH(Sheet3!$B$2:$J$13,{"Being resolved","Noticed problem"},0)))),TRANSPOSE(COLUMN(INDIRECT("B:J")))^0),{5,6,7},0))*(Sheet2!$B$2:$B$13="Male")*(Sheet2!$C$2:$C$13="QPV"))

By the way, is this a contest or something where you have to be so constrained?