Hello,
In the attached sheet I would like to pull data based on the (dynamic) criteria found in column B of "Sheet1". The caveat is I would like to do this on a quarter-to-date basis depending on the current month entered in cell B2. I have summed Jan - Feb for two accounts as rudimentary examples of what I'm seeking. Any help is appreciated.
In E6 and copy down
NOTE: Month name changed to month number.
=SUMPRODUCT('Opex Pull'!$C$7:$F$35*(('Opex Pull'!$B$7:$B$35=$B6)*(ISNUMBER(MATCH('Opex Pull'!$C$6:$F$6,ROW(INDIRECT(1+3*INT(($B$2-1)/3)&":"&$B$2)),0)))))
In the title it is YTD but in workbook It is given as QTD.
This formula is for QTD.
For Feb it sums Jan+Feb.
For Sep it sums Jul+Aug+sep.
Last edited by kvsrinivasamurthy; 09-06-2022 at 12:42 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thank you this works well...how can I alter the formula to tie in the variable found in B4 and B1 (Sheet 1)? I should have mentioned that the text in rows B4 and B2 of "Opex Pull" could change.
In E6
=SUMPRODUCT('Opex Pull'!$C$7:$F$35*(('Opex Pull'!$B$7:$B$35=$B6)*('Opex Pull'!$C$4:$F$4=$B$1)*(('Opex Pull'!$C$2:$F$2=$B$4)*(ISNUMBER(MATCH('Opex Pull'!$C$6:$F$6,ROW(INDIRECT(1+3*INT(($B$2-1)/3)&":"&$B$2)),0))))))
Please
An other option:
Use month numbers in the input and convert them into monthly names in the output, where necessary.
Try the following formula in E6 and copy down:Formula:
=SUM('Opex Pull'!$C$7:$F$35*(B6='Opex Pull'!$B$7:$B$35)*($B$2>='Opex Pull'!$C$6:$F$6))
The formula in the workbook and above calculates YTD. If you want QTD, add the red part to the formulaFormula:=SUM('Opex Pull'!$C$7:$F$35*(B6='Opex Pull'!$B$7:$B$35)*($B$2>='Opex Pull'!$C$6:$F$6)*(INT(($B$2-1)/3)=INT(('Opex Pull'!$C$6:$F$6)-1)/3))
Last edited by HansDouwe; 09-06-2022 at 09:37 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks