[RESOLVED] SUMIF with two conditions?

[drag0n_45]

Hi there,
I'm using OpenOffice Calc (which seems to have many of the same built-in functions as Excel), and I'm looking to perform a SUMIF with using two comparisons, and I'm not quite sure how to do it. I'm wondering if SUMPRODUCT would work better.
In Excel, I've still been unable to do this, so I know it's not a compatibility thing.

I'm doing my step-dad's taxes, and trying to make receipt categorization for his home business easier. He knows not Excel (or Calc) at all, so I'd like to make this stupid-proof.

The spreadsheet is lain out with a numeric code for each category of receipts (office supplies, fuel, repairs, etc), a receipt date, and an amount. Each category needs to be summarized by date. I'd like to make it as easy as allowing him to enter receipts for any category on any day and having Excel (Calc) perform the operation.

I've figured out how to get a summary of all receipts for all months using (pseudocode):

=sumif(month_column,end of month using next month-1 function, dollar_column)-sumif(month_column,1,dollar_collumn)

and I've figured out how to get a summary of all receipts for all codes using a simple SumIf statement, but I need to combine them. I need a summary of all codes for a given month. I can convert from Excel to Calc, but I just don't know how to get this union working...

Any help would be beyond appreciated, as I've been racking my head for awhile trying to figure this out.

Thanks ahead of time.

[zaza]

You can't combine them within the sumif, but you could concatenate the month and code together in a separate column and then sumif against that.

ie, Cell D4 = month & "\" & code

to create a key in column D

Then sumif against D.

[drag0n_45]

Good idea.

[boswald]

Here is how I would do it in Excel:

Use a sum() function inserted as an array formula (hit ctrl-enter instead of just enter) like this:

=SUM(($M$3:$M$248=$T3)*($L$3:$L$248=ScheduleType1)*($K$3:$K$248))

It will look likle this with curly brackets when you click on the cell:

{=sum(....)}

What this does is the first set of parenthesis gives an array of "1"'s for each row in the range that matches what is in T3 (you can use a text value if you want), which is then multiplied by an array of "1"'s for everything in the second range that matches the text "SheduleType1" which is then multiplied by the values I want to extract and sum. You can combine multiple condiitions, so your example may have someting like:
=Sum((range1=condition)*(range1valuesToExtract)-(range2=condition)*(range2ValuesToExtract))
Hope this can be of help.
Brad

[drag0n_45]

I'm not sure you can do array formulas on OO. I'll let you know how that works out when I get a chance.

[si_the_geek]

For Excel formulas you can use the OR/AND functions, eg:

=SumIf( And(condition1,condition2) , ...

[drag0n_45]

Si,
I'm not sure if you can do that in OO. Though if I remember right, I tried that and the problem I ran in to was that I could not evaluate a range of dates against one cell.

I'd need to play with it in application though - not in front of it right now.

[drag0n_45]

Array fomulas seemed to have worked. Not quite sure how to isnert them into OO, but I worked on the cell in Excel and then opened it in OO and it seems to have worked without issue. thanks again! Formula below...

=SUM((MONTH($'Trip Receipts'.$F$2:$F$1000)=C$14)*($A16=$'Trip Receipts'.$E$2:$E$1000)*$'Trip Receipts'.$G$2:$G$1000)