How should I set the formula to get the figures with multiple criteria in merged cell? E.g. refer to the Master table, if I would like to know how many orders that Peter has made in Area 2 in Jan. I tried to use index match function, but didn't work out. The criteria should go through > select "order" in column A > "Area 1" in column B > "Peter" in Column C > to have the row number, and match "2017/Jan" in column D. Can anyone suggest a formula to me?
1
votes
1 Answers
0
votes
The same has been answered by A.S.H through comment, hence reflecting as unanswered.
Posting the answer behalf of A.S.H
Dealing with merged cells in formulas in a well-known tedious problem, especially when the formulas involve arrays, such as Index / Match/ SUMIF etc. If you can't Unmerge those cells, your best bet would be to use a hidden helper column.
My Suggestions:
Steps You Can Follow In These Scenarios
- Create Name Manager by entering the cell ranges manually.
Formulas -> Name Manager -> New... -> Enter Group Name (Ex.Raw data) -> Select Where You Want To Apply the Scope -> Type Ranges as you required references (ex. =Sheet1!$A$1:$A$4)
When you typing the formula instead of array or match ranges you can directly use the Group Name that you have entered.
You can create helper column to fix the issue.
Type the cell ranges manually.
Use Concatenate Methods.
Index/Match/SUMIFetc. If you can't unmerge those cells, your best bet would be to use a hidden helper column. - A.S.H