How to use SUMIFS & Include 'All' to Drop-down IndexMatch
Requirement:
The user has a data table that contains Entertainment and Travel categories, classified by Location and Department.
The user is using the SUMIFS slice the data table based on the drop-downs, but would like to have an "All" option in each drop-down, where the SUMIFS no longer includes that parameter.
Solution:
=SUMIFS(KRTable[Amount],KRTable[Category],B7,KRTable[Location],IF(B$4="All","*",B$4),KRTable[Dept],IF(C$4="All","*",C$4))
Obtained from the OzGrid Help Forum.
Solution provided by AliGW.
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 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
