Jan 16 2023 06:24 PM
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)
Jan 17 2023 01:24 AM
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))
Jan 17 2023 05:37 PM
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
Jan 17 2023 07:59 PM
Jan 18 2023 01:41 AM
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))