Combine VLOOKUP and SUMIFS

Please see attached
Input Data : Col A to Col F
Output data : Col G : Col K

I have created a SumIF formula that calculates the goals for and goals against the team.
However I want to amend is so that is will match the name against column H and populate the relevant data.

For example.
If Team name = ABC then it does a vlookup (col A to Col F) and match the name against Row I4 then
Under Col I and H: uses the SUM IF formula to show the correct data.


I tried using the under cell I3 but it returns #VALUE!

=SUMPRODUCT(--(B:B=H3),SUMIF(C:C,$D$2,D:D)+SUMIF(C:C,$E$2,E:E))



A formula to show the below correct data.
Name For Against
ABC 10 5 5
XYZ 8 5 3

Thank you for the prompt response.

The data will be added as more games are played.

I would like to create a formula/macro that will automatically populate the data of the individual names on the next available row.(under the correct worksheet)

therefore Name = Worksheet as per below.

Sheetname : ABC
DATE NAME TEAM Red Black status
06/11/2019 ABC RED 5 2 W
07/11/2019 ABC BLACK 3 5 W

Sheetname : XYZ
DATE NAME TEAM Red Black status
10/11/2019 XYZ RED 2 4 L
10/11/2019 XYZ BLACK 1 6 W


Is there an easy way to do this!

Worksheet DATADUMP contains a list of all the data. This sheet will be updated on a regular basis.


OUTPUT SHEETS: A,B,C,D


If Name = A under DATADUMP sheet then all information will be copied to worksheet A
If Name = B under DATADUMP sheet then all information will be copied to worksheet B
If Name = C under DATADUMP sheet then all information will be copied to worksheet C
If Name = D under DATADUMP sheet then all information will be copied to worksheet D

Any new data entered under the input sheet will automatically be copied to the relevant Output worksheet at the next available row.

Please see attached. Hope this help!

thank you. It worked.

How to I add it so it picks up wildcards - for example if name = "A" or name = "A 123" or name = "A Extra" then it will be displayed under worksheet A

Noted, Real list as follows:

If suffix is a number or "Extra" or "Test" then add to the same sheet as name. (more suffix words can be added later but will not coincide with surname eg adam smith with be another worksheet as per below)

NAME: ADAM, BOB, CATHERINE, DAVID, ADAM SMITH , ADAM EXTRA, ADAM SMITH 543, CATHERINE TEST ,DAVID EXTRA, ADAM 123 & AADAM

Worksheet ADAM to show ADAM & ADAM EXTRA & ADAM 123
Worksheet ADAM SMITH to show ADAM SMITH & ADAM SMITH 543
Worksheet AADAM to show AADAM

Worksheet BOB to show BOB
Worksheet CATHERINE to show CATHERINE & CATHERINE TEST
Worksheet DAVID to show DAVID & DAVID EXTRA

Hope that clarifies.

Hi Kenrub1,

Or you could do it using a Pivot Table - see attached.

No formulas or Macros.

Regards

peterrc

If I place and underscore or a dot therefore adam_smith or adam.smith can the formula work?
Alternatively I can split it and have two columns - first name and surname?

any updates?

sorry - totally missed that.
I will have a look. Thank you. Much appreciated.

Thanks. It works like a charm!

I have one final request. See attached K5:L8
1. Show total games played where name = cell (A1)
2. Display the status of the last three games.

This formula does not work =INDEX(F:F,INDEX(MAX(($F:$F<>"")*(ROW(F:F))),0))

Requirements:
1. If status is blank then go to the next cell up.
2. If name is not equal to cell A1 then go to the next cell up.

Datadump sheet will be atleast 1000 rows of data.
Sheet with name (adam.smith) will be atleast 200 rows
Total number of sheets approx 20 sheets which contain the formulas
I will then add more sheets and macros to complete the workbook.

This is correct!

If I do not have a space then the data is populated as below.
DATE NAME TEAM DR CREDIT status goals Red Black
12/11/2019 Adam.Smith 00/01/1900 0 500 00/01/1900 0 0 0

Required as per file:
DATE NAME TEAM DR CREDIT status goals Red Black
12/11/2019 Adam.Smith 500


If formula can we amended to not display if cell is empty or blank then I will not need the space.

ok thank you.


If there is no values, then this formula shows #NUM!

=INDEX(F:F,AGGREGATE(14,6,ROW($F$3:$F$21)/(($B$3:$B$21=$A$1)*($F$3:$F$21<>"")),ROWS(L$6:L6)))

If the sheet has no data, for example sheet called BOB, then the last game returns #NUM!
If Sheet contains only one row then then the second last and third last game returns #NUM!
etc

thanks. One final request.
I want to create a dynamic range.
The below formula creates the range for all with the formula. I only want range with data (no blank cells with formulas).

For example under Adam.Smith I have copied formulas from A2:I20 but the data only is from A2:15 then the range should only be from A2:I5 (which will dynamically change as more data is entered)

Sub Range()
Dim myrange As String
myrange = Cells(Rows.Count, 9).End(xlUp).Address
ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange
End Sub

I think there is a misunderstanding.
What I want to to create a dynamic range. Therefore when I print the worksheet, I will only print the rows where there is data.
Currently when I print, it still prints all the other rows where we have put in in the formula.

Adam.Smith should only print from A1:I4 (this range keeps changing as we add more data)