Forum Discussion
another match/search and return value challenge
Sorry, my mistake. It should have been
=SUMIFS(INDEX(Sheet4!$B$13:$K$46,0,MATCH(C$2&C$3,Sheet4!$B$10:$K$10,0)),Sheet4!$A$13:$A$46,$A10,Sheet4!$L$13:$L$46,$A$8&$A$9&C$1)/1000
In C48:
=SUMIF(Search!$A$2:$A$37,$A48,INDEX(Search!$B$2:$Q$37,0,MATCH(C$2,Search!$B$1:$Q$1,0)))Fill to the right to column F, then down to row 50 (or vice versa).
In H48:
=SUM(C48:F48)Fill down to row 50.
HansVogelaar Thanks for this! It worked again.
Just to add complexity -- I would like to add a 'check' by adding a Total (Row 52) vs =sum function. Filtering the data, I can trust Row 52 -- but now, the 53, 54 and 55 are not following.
I'm afraid I don't understand what you are trying to do now. Can you explain?
HansVogelaar Hi Hans, sorry I wasn't clear at all.
Basically, rows 53 and 54 in total should equal row 52, with a formula that would match column A and column C, row 1, row 6, etc.
Something like, if 'CC_ABCD - Apples' & 'All other OPEX' & '2018.Q1' matches data in Search tab in Column S, then return value. Sorry, I'm not good at this.
There is no reason rows 53 and 54 should add up to row 52.
The formulas in row 53 and 54 sum all values on the Search sheet for Apples and Bananas, but those rows have both IHQInternational ManagementHR / Staff Costs and IHQFruitsAll other OPEX in column R.
The formulas in row 52 sum the values for IHQFruitsAll other OPEX only, not those for IHQInternational ManagementHR / Staff Costs