Sum multiple columns - using sumifs / index / match?

[MixedUpExcel]

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.

[TABLE="width: 1174"]
<colgroup><col><col><col span="12"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[TD]Col G[/TD]
[TD]Col H[/TD]
[TD]Col I[/TD]
[TD]Col J[/TD]
[TD]Col K[/TD]
[TD]Col L[/TD]
[TD]Col M[/TD]
[TD]Col N[/TD]
[TD]Col O[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][/TD]
[TD]Sales Value[/TD]
[TD][/TD]
[TD]Sales Value[/TD]
[TD][/TD]
[TD]Sales Value[/TD]
[TD][/TD]
[TD]Sales Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sales Value[/TD]
[TD]Sales Value[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 7[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 8[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 12[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 13[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 14[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 15[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 16[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 17[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 18[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 19[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 20[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 21[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 22[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 23[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 24[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 25[/TD]
[TD]Display[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 26[/TD]
[TD]Direct[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Row 27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 28[/TD]
[TD]Total Direct[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]720[/TD]
[TD="align: right"]840[/TD]
[TD="align: right"]960[/TD]
[TD="align: right"]1080[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1320[/TD]
[TD="align: right"]1440[/TD]
[/TR]
</tbody>[/TABLE]

The answer should be 4680 but I'm getting 120.. what am I doing wrong please?

=SUMIFS(INDEX(D:O,,MATCH("Sales Value",$D$3:$O$3,0)),$C:$C,"Direct")

Thanks.

 

[XOR LX]

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]

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]

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

 

[MixedUpExcel]

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

Cheers

Thanks again for your help.

Simon