Sanjeev1976
Active Member
- Joined
- Dec 25, 2008
- Messages
- 486
- Office Version
- 365
- Platform
- Windows
Can we achieve the below using single sumproduct formula in cell B8 :
Data Presentation.xlsb | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | |||
2 | A | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | ||
3 | B | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | ||
4 | |||||||||||||||||
5 | X | 25 | 25 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | 26 | ||
6 | Y | 20 | 20 | 21 | 21 | 21 | 21 | 21 | 21 | 21 | 21 | 21 | 21 | 21 | 21 | ||
7 | |||||||||||||||||
8 | Sumproduct | 5,500 | 5,500 | 5,750 | 5,750 | 5,750 | 5,750 | 5,750 | 5,750 | 5,750 | 5,750 | 5,750 | 5,750 | 5,750 | 5,750 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C1:O1 | C1 | =B1+1 |
B2:AX3 | B2 | =FILTER(Format!$D$4:$AZ$32,(Format!$D$4:$D$32<>"")*(Format!$D$4:$D$32<>0)) |
B5:AX6 | B5 | =FILTER(Format!$D$35:$AZ$2032,Format!$B$35:$B$2032=Format!$B36) |
B8:O8 | B8 | =SUMPRODUCT(B2:B3,B5:B6) |
Dynamic array formulas. |