Totals for month if 2 conditions are met

  • Thread starter Thread starter Carrach
  • Start date Start date
C

Carrach

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.
 
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
 
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.
 
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.
 
Back
Top