Tracing and summing from numerous sheet (sumifs)

[maniacs205]

hello,

Referring to a closed thread : https://www.excelforum.com/excel-for...ml#post5641609

sorry i think i have a better way to explain it, but then why was that thread closed ?

I made a v2 excel sheet so let me re-explain it here :
---------------

So basically in sheet 'Dashboard' I need to fill in colomn B and C with a formula, let's try with channel category A1.

so in 'Reference - Channel' we know A1 it's Channel & Sub-Channel are B5 & C15

Where in 'Reference - ID' the ID for Channel & Sub-Channel of B5 & C15 are IO-1610007498

so in 'Backend Data - Ref ID' Booking No. ID IO-1610007498 has a Revenue of 132,85

While in 'Google Analytics' please ignore the Revenue colomn, since we'll use Revenue 'Backend Data - Ref ID' sheet instead, so let us just trace the Transactions of B5 & C15 -> 1144

In conclusion, we got A1's Valid Transaction & Valid Revenue : 1144 & 132,85

Thank you,

[JohnTopley]

in "Dashboard"

in B3

INDEX('Reference - ID'!$A$2:$A$15,MATCH(1,('Reference - ID'!$B$2:$B$15=VLOOKUP(A3,'Reference - Channel'!A2:C17,2,0))*('Reference - ID'!$C$2:$C$15=VLOOKUP(A3,'Reference - Channel'!A2:C17,3,0)),0))

in C3

=IFERROR(VLOOKUP(INDEX('Reference - ID'!$A$2:$A$15,MATCH(1,('Reference - ID'!$B$2:$B$15=VLOOKUP(A3,'Reference - Channel'!A2:C17,2,0))*('Reference - ID'!$C$2:$C$15=VLOOKUP(A3,'Reference - Channel'!A2:C17,3,0)),0)),'Backend Data - Ref ID'!$A$2:$B$15,2,0),"")

BOTH are Array entered (Ctrl+Shift+Enter)

[maniacs205]

very good, thanks