Summing if two conditions match.

Entropy · General User Joined: 09 May 2006 Posts: 8

I'm hoping someone can help me. My brain hurts after hours of trying to figure this out.

In simplistic terms I need to create a formula that looks for matches within the same row of two columns and takes the amount of the third column and adds that. To be exact...

If column A has shows anything from 10:00 to 10:59 and column B has the text "eurchf" then take the amount in column C and add that for any row meeting the criteria. The rows can span up to 5000.

I appreciate the help. Thanks!

Villeroy · Posted: Sat Apr 25, 2009 11:16 am Post subject:

http://www.oooforum.org/forum/viewtopic.phtml?t=83306
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Entropy · General User Joined: 09 May 2006 Posts: 8

Thank you, the SUMPRODUCT function was an enormous help.

This is what I have so far and it works.

=SUMPRODUCT(Raw.C2:Raw.C5000="22";Raw.E2:Raw.E5000="eurchf";Raw.N2:Raw.N5000)

However the "22" is really only two characters within the cell such as "2009.04.13 22:00"
I'm thinking along the lines of using MID(Raw.B2;12;2), but I'm not sure how to implement it within the first SUMPRODUCT condition.

Ultimately it needs to only calculate data from 22:00 to 22:59 hour timestamps, if that makes any suggestions easier.

Thanks for the help.

Villeroy · Posted: Sun Apr 26, 2009 9:19 am Post subject:

HOUR(xxx)=22

I hate fumbling with text values in Calc, but this should work as long as we work under a locale where "HH" means "Hours":
TEXT(Raw.B2;"HH")="22"
The first numeric approach is unambiguous.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org