# convert formula to excel from open office.

Copper Contributor

# convert formula to excel from open office.

i need to create a formula in excel.  i am trying to convert from OpenOffice.

sheet A is basically a checkbook. column A has the dates, column C has the dollar amounts, column G contains various accounts and column H has been separated into various subaccounts.

sheets B thru M are the months.  each column has been separated into the accounts from sheet A column G. each row has been separated into the various sub accounts from sheet A column 4.

i enter the data on sheet A - columns A, G and H,  and I want the dollar amount to go into the correct month worksheet (sheets b thru m) and into the correct cell for the accounts(column) and subaccounts(rows)

This is what I used in OpenOffice:  =SUMPRODUCT(Checkbook.\$D\$18:\$D\$1999;YEAR(Checkbook.\$A\$18:\$A\$1999)=2023;MONTH(Checkbook.\$A\$18:\$A\$1999)=7;Checkbook.\$G\$18:\$G\$1999=5;Checkbook.\$H\$18:\$H\$1999=50)

4 Replies

# Re: convert formula to excel from open office.

The easiest solution is probably to save the OpenOffice workbook as an Excel workbook (*.xlsx).

The Excel version could look like this:

=SUMPRODUCT(Checkbook!\$D\$18:\$D\$1999*(YEAR(Checkbook!\$A\$18:\$A\$1999)=2023)*(MONTH(Checkbook!\$A\$18:\$A\$1999)=7)*(Checkbook!\$G\$18:\$G\$1999=5)*(Checkbook!\$H\$18:\$H\$1999=50))

# Re: convert formula to excel from open office.

I really don't need a multiplication formula, just a copy and paste to the monthly sheets which have the formulas for the month, account and subaccount

# Re: convert formula to excel from open office.

I think this is closer to what i want in my actual workbook :
=SUMIFS(Checkbook!\$D\$18:\$D\$1999,Checkbook!\$A\$18:\$A\$1999,”>=”&\$a1, checkbook!\$A\$18:\$A\$1999,”<=”&eomonth(\$a1,0),Checkbook!\$G\$18:\$G\$1999,4,Checkbook!\$H\$18:\$H\$1999,50)

in the January worksheet I have January in cell a1. In the Checkbook column "C" is checks and column "D" is deposits

but it doesn't work, it isn't picking up the date range. I am following information on eomonth data on the web. Not sure if I need the \$ or &