Gabriel222
New Member
- Joined
- Oct 24, 2008
- Messages
- 14
- Office Version
- 365
- Platform
- Windows
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
Sheet 2
Sheet 3
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 |