I need a formula that will display a total paid, (Only) if specific months are the current month and the year is equal to the current year shown in cell G1. I've looked into the use of AND(OR as well as OR but but could not figure out how to go about it because there would be a formula within a formula. I have included an example of the sheet with a cell to display the result. Help !
1. Enter month value 3, 6,9,12 in I10 to J10 respectively (I didn't see anything per your criteria 1)
2. I11=OFFSET($E$1,SUMPRODUCT((YEAR($G$1)=YEAR($A$11:$A$1100))*ROW($A$11:$A$1100))-2+COLUMN(A1),) copy to J11
Formula needed to show the amount of payment only if:
1) the current Month is one of the ones shown in cells i10 through L10 i.e. Mar,Jun ,Sept,Dec
2) and the year is equals the one shown in cell G1
What do you expect to see in April ? and in June?
Last edited by JohnTopley; 03-26-2024 at 02:34 PM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
My apologies to everyone. The note shown on the work sheet you saw inaccurately read "The current month is one of the ones shown in cells i10:L10 . i.e. Mar, Jun, Sept, Dec"..
This a mistake, the cell i11 is supposed to only show payments made in the following months as the billing is every three months not monthly.
The billing periods are March, June, September & December otherwise no calculation should take place.
* Of note a separate sheet that contains data obtained for every month of the year also needs this formula but the difference is that the formula will only be in the month cells for March, June, September & December. So in short that page will have cells that contain the formula for those mentioned months not any amount for other months. So How would I modify the provided formula so that March only looks for a payment in -
March, June only looks for a payment in June and so on.?
Last edited by Fred Houck; 03-26-2024 at 06:32 PM. Reason: Additional information not mentioned
You could transform your table into a named table and use it in your formulas for better reading them.
Formula:
Please Login or Register to view this content.
This formula seems to be what you want in cell G7. Is it?
Formula:Please Login or Register to view this content.
Formula for the years.
Formula:Please Login or Register to view this content.
In Excel 365 SUM and SUMPRODUCT are equivalent for the most part.
Would you mind explaining your formula as I completely don't understand it.
I searched the formulas for "TCovPer" but couldn't find anything. The separate spreadsheet the has the months Jan - Dec requires a formula for each of these months and should only pull data for the year shown in G1 i.e. The month of March, the month of June, the month of September, and the month of December. Each of these months are to pull data strictly for the specific month. I do not see how your formula pulls this data for each of these months.
'TCovPer' is the name of the new named table that I created for you and that make it easier to work with Excel formulas.
In between brackets TCovPer[Total PD] is the name of the column.
I didn't see any place for separate months like you are pointing, could you show it more clearly?
Last edited by DJunqueira; 03-27-2024 at 12:25 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks