Ask Your Question
0

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

asked 2014-10-02 01:03:22 +0200

slowrunner gravatar image

updated 2016-03-04 18:10:01 +0200

Alex Kemp gravatar image

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 flag offensive 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

1 Answer

Sort by » oldest newest most voted
2

answered 2014-10-02 04:04:54 +0200

Rugslug gravatar image

It's not a defect:

Click here for MS Excel Hints on SUMIF

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))
edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-10-02 01:03:22 +0200

Seen: 1,887 times

Last updated: Oct 02 '14