MixedUpExcel
Board Regular
- Joined
- Apr 7, 2015
- Messages
- 218
- Office Version
- 365
- Platform
- Windows
Hi,
I have a table of data with different headers at the top and criteria on the left.
I want to sum all values which meet 2 criteria's: Any 'Sales Value' Column with any 'Direct' Row.
The Rows aren't fixed and I have a lot more data variable number of Rows each week when updated. This is just an example table.
<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>
The answer should be 4680 but I'm getting 120.. what am I doing wrong please?
Thanks.
I have a table of data with different headers at the top and criteria on the left.
I want to sum all values which meet 2 criteria's: Any 'Sales Value' Column with any 'Direct' Row.
The Rows aren't fixed and I have a lot more data variable number of Rows each week when updated. This is just an example table.
Col C | Col D | Col E | Col F | Col G | Col H | Col I | Col J | Col K | Col L | Col M | Col N | Col O | |
Row 2 | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
Row 3 | Sales Value | Sales Value | Sales Value | Sales Value | Sales Value | Sales Value | |||||||
Row 4 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 5 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 6 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 7 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 8 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 9 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 10 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 11 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 12 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 13 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 14 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 15 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 16 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 17 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 18 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 19 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 20 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 21 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 22 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 23 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 24 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 25 | Display | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 26 | Direct | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 | 120 |
Row 27 | |||||||||||||
Row 28 | Total Direct | 120 | 240 | 360 | 480 | 600 | 720 | 840 | 960 | 1080 | 1200 | 1320 | 1440 |
<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>
The answer should be 4680 but I'm getting 120.. what am I doing wrong please?
Code:
=SUMIFS(INDEX(D:O,,MATCH("Sales Value",$D$3:$O$3,0)),$C:$C,"Direct")
Thanks.