Hi,
I've been struggling to put together a formula to calculate (sum) monetary values based on two criteria. Unfortunately, I cannot share the file as the data is proprietary so will attempt to give a basic description of what I have and what I would like the output to be (example of excel file attached)...
I have the following:
Tab 1: There are "projects" that have been grouped that are enumerated here, and the respective "sum" of the monies spent (these are accrual based over 10 years) based on monthly revisions. The sums are predicated on a specified month in a cell. (i.e. column A has all grouped projects, column B has all the summed accrual values based on a month as specified by cell E4, and the source data is Tab 2.) An example of a formulas I tried was =SUMIF('Old SIGMA Data Extracts - Hide'!$B$7:$B$775,'Vote 5 (Large vs. Small)'!B4,'Old SIGMA Data Extracts - Hide'!$P$7:$AA$775) or =SUMIFS('Old SIGMA Data Extracts - Hide'!$P$7:$AA$775,'Old SIGMA Data Extracts - Hide'!$B$7:$B$775,'Vote 5 (Large vs. Small)'!B4,'Old SIGMA Data Extracts - Hide'!$P$6:$AA$6,'Vote 5 (Large vs. Small)'!$E$4), with B4 being the grouped project name and E4 being the chosen month, etc.
Tab 2: A drop down menu allows to pick month that have data and compare to the last (i.e. month 5 vs month 4, or May vs. April). Column A has the project group names, B has the total accrual values for the grouped projects is shown for the chosen month (via drop down), Column C has the last month's total accrual (last month vs. what is chosen per drop down in column B), and D is the difference between the two months. I've tried similar formulas as per above, with variations here and there, and the result being similar (i.e. SUMIF formula but =SUMIF()+SUMIF() or =SUMIF()*SUMIF(), or =SUM(INDEX(), MATCH() + MATCH()) or =SUM(INDEX(), MATCH() * MATCH()) variations, =SUMPRODUCT() as well)
Tab 3: Here, all the "projects" have been grouped under a larger project name, and each investment has a specific accrual value on a monthly basis. These monthly accrual values per investment are listed in columns P through AA, starting from month 4 (April) and ending at month 3 (March) (i.e. fiscal year is from April 1 2022 to March 31 2023) (i.e. Column B has the "grouped project" name, Column P has April's accrual data, Q has May's accrual data, etc., until AA which has March's accrual data). AB onwards is truly the source data that comes from SAP, as entered by project managers and financial managers, and added to the last sheet every month.
I would like two macros or formulas:
1. In tab 1, calculates that total accrual value, per project group, on a chosen month (cell E4 drop down)
2. In tab 2, does basically the same as per tab one, with the difference showing from chosen month to prior month based on the drop-down menu.
So far, I have tried various formulas (as above) but get the wrong accrual data, or get only the first accrual data point (using sum, index and match), etc. I do not know if it is having problems because of the date being used in a cell (or month specifically, and the way it is formatted) and doesn't understand the data, or if it has something to do with the dimensions of the matrix, etc. I have never taken any Excel courses, so only know basic ways to manipulate data. I try looking up solutions to my problems online but have had a tough time trying to figure out this particular issue.
Any help or pointers would be greatly appreciated.
I've been struggling to put together a formula to calculate (sum) monetary values based on two criteria. Unfortunately, I cannot share the file as the data is proprietary so will attempt to give a basic description of what I have and what I would like the output to be (example of excel file attached)...
I have the following:
Tab 1: There are "projects" that have been grouped that are enumerated here, and the respective "sum" of the monies spent (these are accrual based over 10 years) based on monthly revisions. The sums are predicated on a specified month in a cell. (i.e. column A has all grouped projects, column B has all the summed accrual values based on a month as specified by cell E4, and the source data is Tab 2.) An example of a formulas I tried was =SUMIF('Old SIGMA Data Extracts - Hide'!$B$7:$B$775,'Vote 5 (Large vs. Small)'!B4,'Old SIGMA Data Extracts - Hide'!$P$7:$AA$775) or =SUMIFS('Old SIGMA Data Extracts - Hide'!$P$7:$AA$775,'Old SIGMA Data Extracts - Hide'!$B$7:$B$775,'Vote 5 (Large vs. Small)'!B4,'Old SIGMA Data Extracts - Hide'!$P$6:$AA$6,'Vote 5 (Large vs. Small)'!$E$4), with B4 being the grouped project name and E4 being the chosen month, etc.
Tab 2: A drop down menu allows to pick month that have data and compare to the last (i.e. month 5 vs month 4, or May vs. April). Column A has the project group names, B has the total accrual values for the grouped projects is shown for the chosen month (via drop down), Column C has the last month's total accrual (last month vs. what is chosen per drop down in column B), and D is the difference between the two months. I've tried similar formulas as per above, with variations here and there, and the result being similar (i.e. SUMIF formula but =SUMIF()+SUMIF() or =SUMIF()*SUMIF(), or =SUM(INDEX(), MATCH() + MATCH()) or =SUM(INDEX(), MATCH() * MATCH()) variations, =SUMPRODUCT() as well)
Tab 3: Here, all the "projects" have been grouped under a larger project name, and each investment has a specific accrual value on a monthly basis. These monthly accrual values per investment are listed in columns P through AA, starting from month 4 (April) and ending at month 3 (March) (i.e. fiscal year is from April 1 2022 to March 31 2023) (i.e. Column B has the "grouped project" name, Column P has April's accrual data, Q has May's accrual data, etc., until AA which has March's accrual data). AB onwards is truly the source data that comes from SAP, as entered by project managers and financial managers, and added to the last sheet every month.
I would like two macros or formulas:
1. In tab 1, calculates that total accrual value, per project group, on a chosen month (cell E4 drop down)
2. In tab 2, does basically the same as per tab one, with the difference showing from chosen month to prior month based on the drop-down menu.
So far, I have tried various formulas (as above) but get the wrong accrual data, or get only the first accrual data point (using sum, index and match), etc. I do not know if it is having problems because of the date being used in a cell (or month specifically, and the way it is formatted) and doesn't understand the data, or if it has something to do with the dimensions of the matrix, etc. I have never taken any Excel courses, so only know basic ways to manipulate data. I try looking up solutions to my problems online but have had a tough time trying to figure out this particular issue.
Any help or pointers would be greatly appreciated.
