Forum Discussion
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 ...
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))
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
- 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 &How about
=INDEX(Checkbook!$D$18:$D$1999, MATCH(1, (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),0))
