Oct 28 2020 04:15 AM
I have a new challenge.
The document is attached for ease of understanding. Basically, I'm trying to retrieve data from the Search tab and return the value in the other tab.
What combination of functions can I use? Thanks (again!).
Oct 28 2020 04:32 AM
With the attachment.
Oct 28 2020 05:00 AM
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.
Oct 28 2020 08:39 AM
@Hans Vogelaar 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.
Oct 28 2020 01:02 PM
I'm afraid I don't understand what you are trying to do now. Can you explain?
Oct 28 2020 01:14 PM
@Hans Vogelaar 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.
Oct 28 2020 01:54 PM
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
Oct 28 2020 02:03 PM
So, rows 53, 54 (and 55 if with values), should add up to row 52, in case Column R in Search tab says IHQFruitsAll other OPEX. So the qualifier will be IHQFruitsAll other OPEX.
If I filer Column R in Search tab, the totals in Column E in Search tab would be 547.9 (divided between CC_43598 - Bananas and CC_ABCD - Apples.)
Oct 28 2020 02:07 PM
@Hans Vogelaar I promise, I'm doing my best to express my thoughts :-).
If A53 in Return Value matches Column A in Search, C1 in Return Value matches Column R in Search, C6 in Return Value matches Row 2 in Search, then return value in Search tab range B4: Q38
Oct 28 2020 02:10 PM
In C53:
=SUMIFS(INDEX(Search!$B$3:$Q$38,0,MATCH(C$6,Search!$B$2:$Q$2,0)),Search!$A$3:$A$38,$A53,Search!$R$3:$R$38,$A$5&$A$52&C$1)/1000
Fill to the right and down.
Oct 28 2020 02:20 PM
@Hans Vogelaar This is pure witchcraft. You solved that in seconds.
Oct 29 2020 09:40 AM
I am attempting to fill cells C10:M12. Qualifiers are: Rows 1,2,3 and Column A -- all in Sheet3. Sheet4 has all the data.
I tried a combination of the formulas you gave me so far, but it doesnt seem applicable on this one since I added a new 'qualifier' which is Row 2.
Oct 29 2020 10:20 AM
In C10:
=SUMIFS(INDEX(Sheet4!$B$13:$K$46,0,MATCH(C$2&C$3,Sheet4!$A$10:$K$10,0)),Sheet4!$A$13:$A$46,$A10,Sheet4!$L$13:$L$46,$A$8&$A$9&C$1)/1000
Oct 29 2020 12:08 PM
This time, I literally copy/pasted the formula onto my test sheet but the answer is not coming up correctly.
I was expecting Row 10,11,12 to add up to Row 9. What am I doing wrong?
Oct 29 2020 12:17 PM
It would seem, that somehow, the values are picking up the next column other than intended: I really don't understand how come.
Oct 29 2020 02:21 PM
SolutionSorry, 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
Oct 29 2020 03:02 PM
@Hans VogelaarIt worked!!!! Thank you!!!
Oct 29 2020 02:21 PM
SolutionSorry, 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