How to use SUMIF using Variable Columns
Requirement:
The user is looking to sum the cells only up to the column of the selected month: O12.
O12 is a drop down of all 12 months.
In the example below, the selected month is Apr-15 so in cell AZ20, the user will need the sum of O20:Q20, columns prior to Apr-15.
If the month changes to May-15, I will need the sum of O20:R20 to sum in cell AZ20.
Solution:
=SUM($O20:INDEX($O20:$S20,MATCH($O$12-1,$O$19:$S$19)))
Obtained from the OzGrid Help Forum.
Solution provided by richardj4.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use SUMIFS and include 'All' to Drop-down IndexMatch |
| How to use SUMIF |
| How to combine LARGE and SUMIF - Array formula |
|
How to use SUMIF to extract attendance details from a register |
| How to use SUMIF using Variable Columns |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
