Excel Sum with Filter & Copy specific Data

[kaken6]

Hello, I have two questions regarding Microsoft excel. If you could
help me I'd really appreciate it!

QUESTION #1
I have 2 worksheets. One has a list of ID numbers (unique - one of
each) and a column for totals (which is blank as of now)

WORKSHEET1
A B
ID #: Total:
1
2
3
4
5

The other has a list of ID numbers (many listed multiple times), has a
number entry which I need to be summed and a date (which I filter by
to get a range)

WORKSHEET2
A B C
1 345 Jan 1
2 327 Feb 10
1 745 March 22
1 237 Dec 6
4 845 Jun 3
3 23 Aug 7
2 2222 Jan 20

What I'm wanting is the entries for each ID to be summed together, but
only for the data that is visible after filtering. Thus, the sumif
function won't work as it also adds the filtered-out data. I can't
get the subtotal function to work either because I only want specific
data corresponding to the individual ID to be totaled.

What I'm hoping the result to look like:

WORKSHEET1
A B
ID #: Total:
1 345+745+237
2 327+2222
3 23
4 845
5 0 [or blank]

....except I want just the final resulting total...not all the xx plus xx
written out


QUESTION #2

Have 2 Worksheets
One looks something like:

WORKSHEET2
ID Name Pattern Colour
Number
1 Bob Stripe
red 547
2 Jill Square
red 322
3 Joe Circle
blue 0
4 Jack Plaid
red 0
5 Paul Curly
blue 77

WORKSHEET1 Right now Column A and Column B are blank for now. In
column C there are numbers that will be linked via vlookup to each
name (don't worry I can do this ) When complete it should look
like:

Name Colour VlookupNumber
Bob red 12
Jill red 3
Paul blue 4
TOTAL =sum(xx)


I need to input just the Name and Colour of each person into the
second chart for those people who have a number greater than 0. I'd
like the Name in column A and the Colour in column B for those
entries. As someone else will be using this file I'm trying to make it
as user-friendly as possible. Is there a way to make it automatically
update when one of the numbers are changed, or a new ID/Name is
changed/added?
Finally, after all the entries I want a TOTAL row, that just totals up
the vlookup values on the row after the last Name. While this seems
like an easy task (=sum(C2:CXX) ) the number of Names in column A will
be changing over time, so it will have to move. Is there any easy way
to do this?

If anyone could help me with one or both questions, I'd greatly
appreciate it. Thanks!!

 

[KC Rippstein]

For your first problem, just use an array formula. In B2,
=IF(Sheet2!$A$1:$A$1000=$A2,SUBTOTAL(9,Sheet2!$B$2:$B$1000),"")
commit using Ctrl+Shift+Enter (commonly called CSE)

For the second problem, you might consider using LARGE. Where sheet1 is
supposed to say Bob, you could enter
=INDEX(Sheet2!$A$2:$E$1000,MATCH(LARGE($D$2:$D$1000,ROW()-1),$D$2:$D$1000),2)
and red would have the same formula but 4 at the end instead of 2. Copy
down 100 rows, put your total row using SUBTOTAL functions, then just apply
an auto filter to only show NonBlanks from column A. This will pull your
subtotals right up underneath the final value (but you have to disengage and
reengage the auto filter whenever data changes).

Hello, I have two questions regarding Microsoft excel. If you could
help me I'd really appreciate it!

QUESTION #1
I have 2 worksheets. One has a list of ID numbers (unique - one of
each) and a column for totals (which is blank as of now)

WORKSHEET1
A B
ID #: Total:
1
2
3
4
5

The other has a list of ID numbers (many listed multiple times), has a
number entry which I need to be summed and a date (which I filter by
to get a range)

WORKSHEET2
A B C
1 345 Jan 1
2 327 Feb 10
1 745 March 22
1 237 Dec 6
4 845 Jun 3
3 23 Aug 7
2 2222 Jan 20

What I'm wanting is the entries for each ID to be summed together, but
only for the data that is visible after filtering. Thus, the sumif
function won't work as it also adds the filtered-out data. I can't
get the subtotal function to work either because I only want specific
data corresponding to the individual ID to be totaled.

What I'm hoping the result to look like:

WORKSHEET1
A B
ID #: Total:
1 345+745+237
2 327+2222
3 23
4 845
5 0 [or blank]

...except I want just the final resulting total...not all the xx plus xx
written out


QUESTION #2

Have 2 Worksheets
One looks something like:

WORKSHEET2
ID Name Pattern Colour
Number
1 Bob Stripe
red 547
2 Jill Square
red 322
3 Joe Circle
blue 0
4 Jack Plaid
red 0
5 Paul Curly
blue 77

WORKSHEET1 Right now Column A and Column B are blank for now. In
column C there are numbers that will be linked via vlookup to each
name (don't worry I can do this ) When complete it should look
like:

Name Colour VlookupNumber
Bob red 12
Jill red 3
Paul blue 4
TOTAL =sum(xx)


I need to input just the Name and Colour of each person into the
second chart for those people who have a number greater than 0. I'd
like the Name in column A and the Colour in column B for those
entries. As someone else will be using this file I'm trying to make it
as user-friendly as possible. Is there a way to make it automatically
update when one of the numbers are changed, or a new ID/Name is
changed/added?
Finally, after all the entries I want a TOTAL row, that just totals up
the vlookup values on the row after the last Name. While this seems
like an easy task (=sum(C2:CXX) ) the number of Names in column A will
be changing over time, so it will have to move. Is there any easy way
to do this?

If anyone could help me with one or both questions, I'd greatly
appreciate it. Thanks!!

 

[Zack Barresse]

Hi there,

For question #1, summing a filtered list for a specific value, you can use
the SUBTOTAL() function, with 9 as the first argument. If you want to sum
specific values in a filtered list itself (with criteria) you need to use a
bit more of a function...

=SUMPRODUCT(($A$2:$A$10=Criteria)*(SUBTOTAL(9,OFFSET($B$2,ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10)),,))))

This function will look in A2:A10, look for 'Criteria', then sum the values
corresponding in B2:B10. Adjust to fit.


Your second question sounds more like you would need some automated VBA code
to do this for you. If this is what you want, right click your worksheet 2
and select View Code, then paste this code in the code pane...


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, rngFind As Range, LastRow As Long, Prompt As String
'Exit if more than one cell selected
If Target.Cells.Count > 1 Then GoTo ExitNow
'Check if a name was entered, our 'trigger' (Name or Colour)
If Target.Column <> 2 And Target.Column <> 4 Then GoTo ExitNow
'Check if Number is greater than zero, from either column
If Target.Offset(0, IIf(Target.Column = 2, 3, 1)).Value <= 0 Then GoTo
ExitNow
'Change this name to your worksheet 1 name
Set ws = ThisWorkbook.Sheets("Worksheet1")
'Find the value on worksheet 1
Set rngFind = ws.Range("A:A").Find(what:=Me.Cells(Target.Row,
"B").Value, MatchCase:=False)
'Turn off application events
Application.EnableEvents = False
'Check if the name was found or not
If Not rngFind Is Nothing Then
'See if the user wants to update the value
Prompt = Me.Cells(Target.Row, "B").Value & " is already entered yet.
Update it now?"
If MsgBox(Prompt, vbYesNo, "UPDATE NEW VALUE?") <> vbYes Then GoTo
ExitNow
'Set Colour value in worksheet 1
rngFind.Offset(0, 1).Value = Me.Cells(Target.Row, "D").Value
Else
'See if the user wants to enter a new value
Prompt = Me.Cells(Target.Row, "B").Value & " is not entered yet.
Enter it now?"
If MsgBox(Prompt, vbYesNo, "ENTER NEW VALUE?") <> vbYes Then GoTo
ExitNow
'Get the first empty row in worksheet 1
LastRow = ws.Cells.Find(what:="*", lookat:=xlPart,
searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
'Set the name as a new value in worksheet 1
ws.Cells(LastRow, "A").Value = Me.Cells(Target.Row, "B").Value
'Set the colour to the new name in worksheet 1
ws.Cells(LastRow, "B").Value = Me.Cells(Target.Row, "D").Value
End If
'Set exit point
ExitNow:
'Turn application events back on
Application.EnableEvents = True
End Sub


Maybe this isn't what you're looking for, but maybe it's a start for you.

Hope This Helps

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"

Hello, I have two questions regarding Microsoft excel. If you could
help me I'd really appreciate it!

QUESTION #1
I have 2 worksheets. One has a list of ID numbers (unique - one of
each) and a column for totals (which is blank as of now)

WORKSHEET1
A B
ID #: Total:
1
2
3
4
5

The other has a list of ID numbers (many listed multiple times), has a
number entry which I need to be summed and a date (which I filter by
to get a range)

WORKSHEET2
A B C
1 345 Jan 1
2 327 Feb 10
1 745 March 22
1 237 Dec 6
4 845 Jun 3
3 23 Aug 7
2 2222 Jan 20

What I'm wanting is the entries for each ID to be summed together, but
only for the data that is visible after filtering. Thus, the sumif
function won't work as it also adds the filtered-out data. I can't
get the subtotal function to work either because I only want specific
data corresponding to the individual ID to be totaled.

What I'm hoping the result to look like:

WORKSHEET1
A B
ID #: Total:
1 345+745+237
2 327+2222
3 23
4 845
5 0 [or blank]

...except I want just the final resulting total...not all the xx plus xx
written out


QUESTION #2

Have 2 Worksheets
One looks something like:

WORKSHEET2
ID Name Pattern Colour
Number
1 Bob Stripe
red 547
2 Jill Square
red 322
3 Joe Circle
blue 0
4 Jack Plaid
red 0
5 Paul Curly
blue 77

WORKSHEET1 Right now Column A and Column B are blank for now. In
column C there are numbers that will be linked via vlookup to each
name (don't worry I can do this ) When complete it should look
like:

Name Colour VlookupNumber
Bob red 12
Jill red 3
Paul blue 4
TOTAL =sum(xx)


I need to input just the Name and Colour of each person into the
second chart for those people who have a number greater than 0. I'd
like the Name in column A and the Colour in column B for those
entries. As someone else will be using this file I'm trying to make it
as user-friendly as possible. Is there a way to make it automatically
update when one of the numbers are changed, or a new ID/Name is
changed/added?
Finally, after all the entries I want a TOTAL row, that just totals up
the vlookup values on the row after the last Name. While this seems
like an easy task (=sum(C2:CXX) ) the number of Names in column A will
be changing over time, so it will have to move. Is there any easy way
to do this?

If anyone could help me with one or both questions, I'd greatly
appreciate it. Thanks!!

 

[smaczylo]

Hi there,

For question #1, summing a filtered list for a specific value, you can use
the SUBTOTAL() function, with 9 as the first argument. If you want to sum
specific values in a filtered list itself (with criteria) you need to use a
bit more of a function...

=SUMPRODUCT(($A$2:$A$10=Criteria)*(SUBTOTAL(9,OFFSET($B$2,ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10)),,))))

This function will look in A2:A10, look for 'Criteria', then sum the values
corresponding in B2:B10. Adjust to fit.

Your second question sounds more like you would need some automated VBA code
to do this for you. If this is what you want, right click your worksheet 2
and select View Code, then paste this code in the code pane...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, rngFind As Range, LastRow As Long, Prompt As String
'Exit if more than one cell selected
If Target.Cells.Count > 1 Then GoTo ExitNow
'Check if a name was entered, our 'trigger' (Name or Colour)
If Target.Column <> 2 And Target.Column <> 4 Then GoTo ExitNow
'Check if Number is greater than zero, from either column
If Target.Offset(0, IIf(Target.Column = 2, 3, 1)).Value <= 0 Then GoTo
ExitNow
'Change this name to your worksheet 1 name
Set ws = ThisWorkbook.Sheets("Worksheet1")
'Find the value on worksheet 1
Set rngFind = ws.Range("A:A").Find(what:=Me.Cells(Target.Row,
"B").Value, MatchCase:=False)
'Turn off application events
Application.EnableEvents = False
'Check if the name was found or not
If Not rngFind Is Nothing Then
'See if the user wants to update the value
Prompt = Me.Cells(Target.Row, "B").Value & " is already entered yet.
Update it now?"
If MsgBox(Prompt, vbYesNo, "UPDATE NEW VALUE?") <> vbYes Then GoTo
ExitNow
'Set Colour value in worksheet 1
rngFind.Offset(0, 1).Value = Me.Cells(Target.Row, "D").Value
Else
'See if the user wants to enter a new value
Prompt = Me.Cells(Target.Row, "B").Value & " is not entered yet.
Enter it now?"
If MsgBox(Prompt, vbYesNo, "ENTER NEW VALUE?") <> vbYes Then GoTo
ExitNow
'Get the first empty row in worksheet 1
LastRow = ws.Cells.Find(what:="*", lookat:=xlPart,
searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1
'Set the name as a new value in worksheet 1
ws.Cells(LastRow, "A").Value = Me.Cells(Target.Row, "B").Value
'Set the colour to the new name in worksheet 1
ws.Cells(LastRow, "B").Value = Me.Cells(Target.Row, "D").Value
End If
'Set exit point
ExitNow:
'Turn application events back on
Application.EnableEvents = True
End Sub

Maybe this isn't what you're looking for, but maybe it's a start for you.

Hope This Helps

--
Regards,
Zack Barresse, aka firefytr
MsgBox "fire" & "fytr" & Chr(64) & "vba" & "express" & Chr(46) & "com"

Hello, I have two questions regarding Microsoft excel. If you could
help me I'd really appreciate it!

QUESTION #1
I have 2 worksheets. One has a list of ID numbers (unique - one of
each) and a column for totals (which is blank as of now)

WORKSHEET1
A B
ID #: Total:
1
2
3
4
5

The other has a list of ID numbers (many listed multiple times), has a
number entry which I need to be summed and a date (which I filter by
to get a range)

WORKSHEET2
A B C
1 345 Jan 1
2 327 Feb 10
1 745 March 22
1 237 Dec 6
4 845 Jun 3
3 23 Aug 7
2 2222 Jan 20

What I'm wanting is the entries for each ID to be summed together, but
only for the data that is visible after filtering. Thus, the sumif
function won't work as it also adds the filtered-out data. I can't
get the subtotal function to work either because I only want specific
data corresponding to the individual ID to be totaled.

What I'm hoping the result to look like:

WORKSHEET1
A B
ID #: Total:
1 345+745+237
2 327+2222
3 23
4 845
5 0 [or blank]

...except I want just the final resulting total...not all the xx plus xx
written out

QUESTION #2

Have 2 Worksheets
One looks something like:

WORKSHEET2
ID Name Pattern Colour
Number
1 Bob Stripe
red 547
2 Jill Square
red 322
3 Joe Circle
blue 0
4 Jack Plaid
red 0
5 Paul Curly
blue 77

WORKSHEET1 Right now Column A and Column B are blank for now. In
column C there are numbers that will be linked via vlookup to each
name (don't worry I can do this ) When complete it should look
like:

Name Colour VlookupNumber
Bob red 12
Jill red 3
Paul blue 4
TOTAL =sum(xx)

I need to input just the Name and Colour of each person into the
second chart for those people who have a number greater than 0. I'd
like the Name in column A and the Colour in column B for those
entries. As someone else will be using this file I'm trying to make it
as user-friendly as possible. Is there a way to make it automatically
update when one of the numbers are changed, or a new ID/Name is
changed/added?
Finally, after all the entries I want a TOTAL row, that just totals up
the vlookup values on the row after the last Name. While this seems
like an easy task (=sum(C2:CXX) ) the number of Names in column A will
be changing over time, so it will have to move. Is there any easy way
to do this?

If anyone could help me with one or both questions, I'd greatly
appreciate it. Thanks!!

Thank you so much Zack and KC.
Both of the issues are resolved thanks to you - I used Zack's code for
summing the numbers, and a method similar to KC's for the second
problem.
Thank you once again for your help!