Sum multiple columns - using sumifs / index / match?

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
206
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.

Col CCol DCol ECol FCol GCol HCol ICol JCol KCol LCol MCol NCol O
Row 2JanFebMarAprMayJunJulAugSepOctNovDec
Row 3Sales ValueSales ValueSales ValueSales ValueSales ValueSales Value
Row 4Direct102030405060708090100110120
Row 5Display102030405060708090100110120
Row 6Direct102030405060708090100110120
Row 7Display102030405060708090100110120
Row 8Direct102030405060708090100110120
Row 9Display102030405060708090100110120
Row 10Direct102030405060708090100110120
Row 11Display102030405060708090100110120
Row 12Direct102030405060708090100110120
Row 13Display102030405060708090100110120
Row 14Direct102030405060708090100110120
Row 15Display102030405060708090100110120
Row 16Direct102030405060708090100110120
Row 17Display102030405060708090100110120
Row 18Direct102030405060708090100110120
Row 19Display102030405060708090100110120
Row 20Direct102030405060708090100110120
Row 21Display102030405060708090100110120
Row 22Direct102030405060708090100110120
Row 23Display102030405060708090100110120
Row 24Direct102030405060708090100110120
Row 25Display102030405060708090100110120
Row 26Direct102030405060708090100110120
Row 27
Row 28Total Direct1202403604806007208409601080120013201440

<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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Hi,

Your construction is summing just the first "Sales Value" column. In any case, you can't pass multiple columns like that to SUMIFS. You need to switch to SUMPRODUCT, though then you'd be strongly advised not to use entire column references, e.g.:

=SUMPRODUCT((D3:O3="Sales Value")*(C4:C26="Direct")*D4:O26)

Regards
 

MixedUpExcel

Board Regular
Joined
Apr 7, 2015
Messages
206
Hi,

Your construction is summing just the first "Sales Value" column. In any case, you can't pass multiple columns like that to SUMIFS. You need to switch to SUMPRODUCT, though then you'd be strongly advised not to use entire column references, e.g.:

=SUMPRODUCT((D3:O3="Sales Value")*(C4:C26="Direct")*D4:O26)

Regards

Thanks for the solution, had a feeling someone might have suggested SUMPRODUCT (I'm not as familiar with using it).

Regarding Column length, would it be ok for me to change the 26 in the formula to something like 5000? which would be overkill for the number of rows I need but nothing like a full Column?

Thanks.
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Regarding Column length, would it be ok for me to change the 26 in the formula to something like 5000? which would be overkill for the number of rows I need but nothing like a full Column?

Sure - 5000 shouldn't cause too much issue, though the lower the better with SUMPRODUCT.

Cheers
 

Forum statistics

Threads
1,141,051
Messages
5,703,960
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top