SUMIF() only first col of range is added [closed]

Libre Office 4.1.5.3 English Mac OS X 10.9.5 English

equation: =sumif(D6:D500,1,B6:C500)

Popup Definition: SUMIF(range,criteria,sum_range)

purpose: two columns of expense values (e.g. cash in col B, check in col C) and several columns of expense type with value 1 if expense is that type (food col D, restaurant col E, misc col F) so if a 1 in the food type column D, then include any value in column B and column C of that row into the total sum.

Only the values in the first column of the sum_range will be added.

Is this a defect or is "sum_range" actually "sum_ROWrange?

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-03-04 18:10:17.851472

Sort by » oldest newest most voted

It's not a defect:

Says:

The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using the upper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument.

You could add two equations of SUMIF:

=SUMIF(D6:D500,1,B6:B500)+SUMIF(D6:D500,1,C6:C500)


Or try SUMPRODUCT:

=SUMPRODUCT((D6:D500)*(B6:C500))

more