CODEWITHSUNDEEP
openoffice.orgopenoffice-calc
Nikolay Yordanov
Nikolay Yordanov

Reputation: 1404

OpenOffice.org Calc SUMIF statement

I'm trying to sum all cells in C2:C2000 if the corresponding cell in column A is in (10; 20], but I'm having problems with the condition.

I've tried something like =SUMIF(A2:A2000, AND(">10","<=20"), C2:C2000), but that returns 0 which is incorrect.

Thanks.

[edit]

It is possible to use =SUMIF(A2:A2000, ">10", C2:C2000) - SUMIF(A2:A2000, ">20", C2:C2000) but that's not exactly what I'm looking for.

Upvotes: 2

Views: 4687

Answers (2)

Ryan Damm
Ryan Damm

Reputation: 21

A relatively elegant solution in OpenOffice is to use Sumproduct -- which multiplies together two arrays. Feed Sumproduct with successive truth tests on the range (which return an array of booleans, I think). Something like this:

SUMPRODUCT(C2:C2000;A2:A2000>"10";A2:A2000>="20")

I got this from here:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=28585

Tested in OpenOffice -- I have no idea if there's an equivalent in Excel. Sorry, I don't have a copy to test on.

Upvotes: 2

Mikeb
Mikeb

Reputation: 6361

The easy way to get around the complex conditions limitation is to use another column as a 'local variable' - make column D the result of your condition (AND(etc)), hide it if you want to have less screen clutter, and use D2:D2000 as the if part of the SUMIF.

edited to add alternate option:

http://support.microsoft.com/kb/275165

use Sum(if( .. )) instead of SUMIF, and use products and sums instead of ANDS and ORs.

Personally, I think using an intermediate column is easier to follow and maintain.

Upvotes: 2

Related Questions