Complex Function: Match names on Two Sheets
 

I am trying to find one name and sum all values in a column; sum would go
into Column G. For instance, I have the employee number in Column A in a
sheet named €˜Filtered List and I want to sum all values in Column E of the
same sheet in the rows where that employee number shows up. The problem is
that the names are not in adjacent rows. I know Sumproduct can overcome this
limitation, but also, I wont know the Employee ID numbers in advance,
because this list is built on the fly, with a macro, so I was hoping to find
a function that I can pop into another simple macro that I have.

Something like this would work:
=SUMPRODUCT((A2:A13=115354)*(E2:E13))

But again, I wont know the Employee ID number in advance, so I cant
necessarily use €˜115354 in my Sumporduct.

Any thoughts?

Thanks,
Ryan---



--
RyGuy

Complex Function: Match names on Two Sheets
 

I don;'t know why I put 'two names on two sheets'. Maybe I wasn't paying
attention (not unusual for me). As stated above, I want to Find employee ID
numbers in Column A in a sheet named €˜Filtered List and I want to sum all
values in Column E of the same sheet; sum all values that correspond to a
certain ID number.


--
RyGuy


"ryguy7272" wrote:

I am trying to find one name and sum all values in a column; sum would go
into Column G. For instance, I have the employee number in Column A in a
sheet named €˜Filtered List and I want to sum all values in Column E of the
same sheet in the rows where that employee number shows up. The problem is
that the names are not in adjacent rows. I know Sumproduct can overcome this
limitation, but also, I wont know the Employee ID numbers in advance,
because this list is built on the fly, with a macro, so I was hoping to find
a function that I can pop into another simple macro that I have.

Something like this would work:
=SUMPRODUCT((A2:A13=115354)*(E2:E13))

But again, I wont know the Employee ID number in advance, so I cant
necessarily use €˜115354 in my Sumporduct.

Any thoughts?

Thanks,
Ryan---



--
RyGuy

Complex Function: Match names on Two Sheets
 

it turned out to be just this:
=SUMPRODUCT(($A$2:$A$13=A2)*($E$2:$E$13))


In VBA:
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT((R2C1:R300C1=RC[-5])*(R2C5:R300C5))"
Range("F2").Select


lastrow = Worksheets("Filtered List").Cells(Rows.Count, "E").End(xlUp).Row
Range("F2").AutoFill Range("F2:F" & lastrow)

Thought it was much more challenging when I first posted.

Anyway, glad it is resolved!!
--
RyGuy


"ryguy7272" wrote:

I don;'t know why I put 'two names on two sheets'. Maybe I wasn't paying
attention (not unusual for me). As stated above, I want to Find employee ID
numbers in Column A in a sheet named €˜Filtered List and I want to sum all
values in Column E of the same sheet; sum all values that correspond to a
certain ID number.


--
RyGuy


"ryguy7272" wrote:

I am trying to find one name and sum all values in a column; sum would go
into Column G. For instance, I have the employee number in Column A in a
sheet named €˜Filtered List and I want to sum all values in Column E of the
same sheet in the rows where that employee number shows up. The problem is
that the names are not in adjacent rows. I know Sumproduct can overcome this
limitation, but also, I wont know the Employee ID numbers in advance,
because this list is built on the fly, with a macro, so I was hoping to find
a function that I can pop into another simple macro that I have.

Something like this would work:
=SUMPRODUCT((A2:A13=115354)*(E2:E13))

But again, I wont know the Employee ID number in advance, so I cant
necessarily use €˜115354 in my Sumporduct.

Any thoughts?

Thanks,
Ryan---



--
RyGuy