CODEWITHSUNDEEP
excel
Rohit Yalavarthi
Rohit Yalavarthi

Reputation: 21

SUMIF to sum multiple columns

I am trying to use the SUMIF function in Excel. I am trying to validate a column in sheet3 and add multiple columns if that condition is met. But my formula:

=SUMIF(Sheet3!E:E,"ABC",Sheet3!K:O)

is adding only one column. The values in column K are being summed but not for all the five columns which I want.

Upvotes: 1

Views: 10383

Answers (1)

user4039065
user4039065

Reputation:

Your best bet is likely the array¹ formula equivalent of your sample formula.

=SUM(IF(E$1:E$9999="abc", $K$1:$O$9999))

Array formulas¹ need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula.

Since your criteria in column E must be met, there is no point in examining any cells below the last text value in column E.

=SUM(IF($E$1:INDEX($E:$E, MATCH("zzz",$E:$E ))="abc", $K$1:INDEX($O:$O, MATCH("zzz",$E:$E ))))

Entered as an array formula. See Guidelines and examples of array formulas for more information.

¹ Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum.

Upvotes: 1

Related Questions