| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totals for month if 2 conditions are met
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Totals for month if 2 conditions are met
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 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Totals for month if 2 conditions are met
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 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 . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP PLEASE Totals for month if 2 conditions are met
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 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Totals Lookup by month and year | Excel Worksheet Functions | |||
| Sum Totals/Different Conditions | Excel Discussion (Misc queries) | |||
| Grouping totals together by month per customer | Excel Discussion (Misc queries) | |||
| keep totals from month to month? | Excel Worksheet Functions | |||
| running totals in a macro for each month | Excel Worksheet Functions | |||
Hybrid Mode
