Prev: wHY DOES MY CURSOR TURN INTO A CROSSHAIR AFTER I CUT AND PASTE SP
Next: Formulas displayed in cell rather than calculated values?
From: Carrach on 20 May 2010 06:29
Still need help with this I'm afraid,
would it help if I use SUMPRODUCT to match the names in some way? I am using
the following in a different sheet.
SUMPRODUCT(--(p2e_Advisor_Last_Name="Thompson"),--(p2e_Advisor_First_Name="Rob"))
could it be inserted into
=SUMPRODUCT(--(Sheet1!A2:A100=A2),--(MONTH(Sheet1!D2:D100)=4),Sheet1!
F2:F100) in order to find the names that match???
Sheet1!A2:A100 is the part that the names are in two columns (A & B) and A2
is the cell containing the full name to match to.
--
any help gratefully received
thanks
carrach


"Carrach" wrote:

> Thank you so much Pete, that worked beautifully for one of my spreadsheets
> and I will be able to use it in many more. However,I do need help withjust
> one more thing:
> one of the sheets has the names split into first_name and last_name in two
> different columns but the sheet I am matching it to has the name in one
> column. I need to be able to change
> (--(Sheet1!A2:A100=A2) from the formula below
> to suit this.
> Sheet1!A2:A100 is the part that the names are in two columns (A & B) and A2
> is the cell containing the full name to match to.
> --
> any help gratefully received
> thanks
> carrach
>
>
> "Pete_UK" wrote:
>
> > Assume your names in Sheet1 are in column A, the dates are in column
> > D, and the values you want to add are in column F. Further assume that
> > the target_name in this_sheet is in A2. Try this formula in a cell in
> > this_sheet:
> >
> > =SUMPRODUCT(--(Sheet1!A2:A100=A2),--(MONTH(Sheet1!D2:D100)=4),Sheet1!
> > F2:F100)
> >
> > Adjust the ranges to suit your data.
> >
> > Hope this helps.
> >
> > Pete
> >
> > On May 17, 4:09 pm, Carrach <Carr...(a)discussions.microsoft.com> wrote:
> > > Hi,
> > > Wonder if anyone could put me on the correct track?
> > > I need to show the totals for the following:
> > > (if target_name_sheet1=target_name_this sheet)and(if
> > > actual_month_sheet1=4)then total actual_value_sheet1for month 4. and place in
> > > cell E6 this sheet.
> > >
> > > (hope you can follow that)
> > > I think that it should be a SUMPRODUCT function but not sure how to put it
> > > together.
> > >
> > > --
> > > any help gratefully received
> > > thanks
> > > carrach
> >
> > .
> >
 | 
Pages: 1
Prev: wHY DOES MY CURSOR TURN INTO A CROSSHAIR AFTER I CUT AND PASTE SP
Next: Formulas displayed in cell rather than calculated values?