SUMIF formula based on non-contiguous ranges
I have to populate a cash-flow forecast, dependent on dates between the programme and a spend profile matching. Usually I would do this with SUMIF, using two rows for each element of the cost estimate (stacking the '1st' date of the month above the cost). However, I need to perform all of the calculations for this project in the same row. Is it possible?
I have tried to liberate solutions to similar queries (using SUMPRODUCT, OFFSET, INDEX etc), but I am never able to get the correct result. This may be due my lack of ability to doctor them, rather than these functions failing to provide the solution I need.
Ideally, I would like to formulate the following. "IF the date in every 5th column in the Spend Profile range matches the date in row 2 of the Programme range, SUM the value(s) in the cell(s) 2 columns prior".
The image below shows a very scaled down version of the data I have to work with. I have manually entered the expected results in the Programme range to illustrate the desired result. nb. CELLS G4 and L4 match S2, therefore E4 and J4 are returned.
You can make it work with SUMIFS with your sample data by offsetting the ranges like this
=SUMIFS($A4:$O4,$C2:$Q2,"1st",$C4:$Q4,S$2)
- How to make Colors Mixer in vba
- How to get HEX color value from cell color?
- SSIS: Insert multiple Excel files with same format but different file name to multiple database table destinations based on the Excel filename
- How to randomize Excel rows
- VLOOKUP Returns 0 even though there is a value in the line below the first result
- transpose a table with multiple values in excel
- Trying to late bind outlook to but getting type mismatch error after create object line
- Power Query Transform a Column based on Another Column
- Apply FILTER formula to array (and use filter criteria on specific column in the array)
- Bitly API call using VBA Excel Macro
- excel vba projects not closing
- In a standard MS Access SQL query output that does not have any aliases, how do I replace the full names by their "first-letters" aliases?
- Sigma or Summation function in Excel
- Problem getting the value from a cell using M in excel
- INDEX MATCH to also pull through notes
- Move formatted data in selection from Excel into Word tables
- Excel VBA function to remove letters at end of string
- Can't get all excel processes to stop when closing through Powershell
- Excel function to construct an array from values without VBA
- Alternative method to delete file with out having to add MS Scripting Runtime Reference
- Add new button to worksheet with Excel Add-in
- Apply outline border only to range of cells with openpyxl
- How to Separate Data with Inconsistent Patterns into a Structured Format in Excel
- Excel - Aggregate dynamically values in a Table using array formulas (MAP, BYROW, CHOOSECOLS, TEXTJOIN, LAMBDA, FILTER, HSTACK) in a single Cell
- How to reference the current User\Desktop location in VBA
- Powerpoint chart to excel relative link
- EXCEL: How do I return multiple columns using XLOOKUP, when the lookup_value is a range?
- VBA: How to Loop IF Statement through a Cell and Chart Series
- Incorporating Checkbox to lines of code rather than Cell value
- How do I remove a character from a column of data in Excel