Match cells on two different sheets to get data

[darxide23]

Hello,

Note: I just finished typing this whole post and realize it's hard to follow and I apologize in advance for that.

Is there a formula that can be used for this situation, or will I need to code it in VBA?

I have a workbook with several sheets, but only two are important for this. Let's call them Sheet1 and Sheet2.

Sheet1 has the following columns:
Name, Type, Data1, Data2

Sheet2 has the following columns:
Name, Type, AdjustedData1,<blank column>Name, Type, AdjustedData2

Sheet1 is sorted by Type first, then by Data1+Data2
Sheet2 has two sorts. On the left, it's sorted by AdjustedData1 and on the right by AdjustedData2.


What I want to do is take the Name from Sheet2 in the order that it appears, then find that name in Sheet1 and get the Data1 from Sheet1. I then want to add all of these Data1 values together and place them in another cell elsewhere.

I understand it's a bit tough to understand. It's hard to explain.


Sheet1:

Name	Type	Data1	Data2
John	A	30	30
Bob	A	15	20
Adam	B	40	25
George	B	10	50
Jack	B	5	15

Types A are adjusted by 10 and 5 for Data1 and Data2 respectively, and types B are only adjusted by 5 for Data2. Only the 3 highest of each Data are kept:

Sheet2:

Name	Type	AdjData1		Name	Type	AdjData2
John	A	40		George	B	55
Adam	B	40		John	A	35
Bob	A	25		Adam	B	30

What I want to do is simply match all of the Names on Sheet2 and get the corresponding data from Sheet1. In this example, I would want to match John, Adam, and Bob from the AdjData1 column and sum their unadjusted Data1 values (85) and place them in a cell somewhere. Then do the same for George, John, and Adam on the AdjData2 side.

Is it in any way possible to do this with a formula or will I need to write new code into the already existing VBA? I'm still learning all of what can be done with formulas but I'm fairly competent at VBA.

[azumi]

Maybe more clear if you upload your small sample workbook at the forum, to attrack the solver see your problem

Cheers

[darxide23]

Maybe more clear if you upload your small sample workbook at the forum, to attrack the solver see your problem

Cheers

It's just a copy of the two tables above, but here you go.

[hemesh]

copy paste below in sheet2 in D2 then hold control and shift together then hit enter to make it array formula
=SUMPRODUCT(--((A2:A4&B2:B4)=TRANSPOSE(Sheet1!A2:A6&Sheet1!B2:B6))*TRANSPOSE(Sheet1!C2:C6))


for data set 2 try below
copy paste below in sheet2 in H2 then hold control and shift together then hit enter to make it array formula
=SUMPRODUCT(--((E2:E4&F2:F4)=TRANSPOSE(Sheet1!A2:A6&Sheet1!B2:B6))*TRANSPOSE(Sheet1!D2:D6))

or Non Array Solution is for data set1
=SUMPRODUCT(SUMIFS(Sheet1!C2:C6,Sheet1!A2:A6,Sheet2!A2:A4,Sheet1!B2:B6,Sheet2!B2:B4))

for data set2
=SUMPRODUCT(SUMIFS(Sheet1!D2:D6,Sheet1!A2:A6,E2:E4,Sheet1!B2:B6,F2:F4))

[darxide23]

copy paste below in sheet2 in D2 then hold control and shift together then hit enter to make it array formula
=SUMPRODUCT(--((A2:A4&B2:B4)=TRANSPOSE(Sheet1!A2:A6&Sheet1!B2:B6))*TRANSPOSE(Sheet1!C2:C6))

Woah, thank you. Is there any chance you could break this down for me so I can understand what it's doing? I'm not not just looking for someone to do a quick fix for me, I want to learn!

Thanks again.

[hemesh]

You are welcome and thanks for the feedback.
=SUMPRODUCT(--((A2:A4&B2:B4)=TRANSPOSE(Sheet1!A2:A6&Sheet1!B2:B6))*TRANSPOSE(Sheet1!C2:C6))

this creates matrix of true(1's) and false(0's) based on match if there is a match that's true else it's false
((A2:A4&B2:B4)=TRANSPOSE(Sheet1!A2:A6&Sheet1!B2:B6)) and then TRANSPOSE(Sheet1!C2:C6)) multiplies numbers available in C2:C6 with corresponding true's and False.

[darxide23]

or Non Array Solution is for data set1
=SUMPRODUCT(SUMIFS(Sheet1!C2:C6,Sheet1!A2:A6,Sheet2!A2:A4,Sheet1!B2:B6,Sheet2!B2:B4))

for data set2
=SUMPRODUCT(SUMIFS(Sheet1!D2:D6,Sheet1!A2:A6,E2:E4,Sheet1!B2:B6,F2:F4))

Thank you for the second option. This formula I actually understand, so it's easier to work with.

[vnkn0vvn]

This doesn't seem to work for me
I was looking for solutions through the forum without success.

Here's what I tried:

=SUMPRODUCT(SUMIFS('Cost Report'!J2:J41,'Cost Report'!B2:B41,Tracker!B9,'Cost Report'!D2:D41,Tracker!D5))

I want a sum based on 2 criterias. The tables are from a separate tab.

I also tried with INDIRECT, however, this confuses me more and does not seem to work.

This works if it's in the same sheet =SUMIFS('Cost Report'!J2:J41,'Cost Report'!A2:A41,A43,'Cost Report'!D2:D41,D43)

Thanks,

[hemesh]

first you don't need to use sumproduct in =SUMIFS('Cost Report'!J2:J41,'Cost Report'!A2:A41,A43,'Cost Report'!D2:D41,D43) because you have only one criteria in each criteria range. above is a simple sumifs construct and this should work. Upload a sample sheet

[vnkn0vvn]

first you don't need to use sumproduct in =SUMIFS('Cost Report'!J2:J41,'Cost Report'!A2:A41,A43,'Cost Report'!D2:D41,D43) because you have only one criteria in each criteria range. above is a simple sumifs construct and this should work. Upload a sample sheet

Please see attached

E9 in Tracker sheet should show result 13 as does F47 in Cost Report sheet.

[hemesh]

It's not working because you have extra trailing spaces in Cell D5 !
it should be "CO00667033-PM01" but with trailing spaces it becomes "CO00667033-PM01 ".

Try removing extra spaces from the Cell D5 or add a trim function to remove unwanted spaces in your text like
=SUMIFS('Cost Report'!$J$2:$J$41, 'Cost Report'!$B$2:$B$41, Tracker!B7, 'Cost Report'!$D$2:$D$41,TRIM(Tracker!$D$5)) try this also in E7 in tracker sheet

[vnkn0vvn]

Right, I got all to work, however, now I am trying to use the source tab to work if I add extra data.

=SUMIFS('Cost Report'!$I$2:INDEX('Cost Report'!I:I,MATCH(9E+99+307,'Cost Report'!I:I),1), 'Cost Report'!$A$2:INDEX('Cost Report'!A:A,MATCH(9E+99+307,'Cost Report'!A:A),1), TRIM(Tracker!B9), 'Cost Report'!$C$2:$C$41, TRIM(Tracker!$D$5))

This works as long as the second criteria range is fixed.I would like to be able to have that similar to the first criteria with Index. The second isn't working as it has text along with values.
Basically would want this to work:

=SUMIFS('Cost Report'!$I$2:INDEX('Cost Report'!I:I,MATCH(9E+99+307,'Cost Report'!I:I),1), 'Cost Report'!$A$2:INDEX('Cost Report'!A:A,MATCH(9E+99+307,'Cost Report'!A:A),1), TRIM(Tracker!B9), 'Cost Report'!$C$2:INDEX('Cost Report'!C:C,MATCH(9E+99+307,'Cost Report'!C:C),1), TRIM(Tracker!$D$5))

Thanks for the help so far!