|
Need a formula to sum with conditions
I am trying to create a summary of data contained in a second worksheet in a
workbook. I need to sum the dollar amouts of Columns S2:V241 but only if the amounts appear in a designated month under the supervisor's name. S5= January (on Data tab), C5= January (on Summary tab) K3:K241 = Supervisor's name (on Data tab), A8:A24 = Supervisor's name (on Summary tab) I have been playing with sumproduct formulas for 2 days now (lol) but just don't have enough experience with this type of formula and can't seem to make it work. Any help/suggestions woud be greatly appreciated! Thank you in advance! -- Will this ever be easy?? |
Need a formula to sum with conditions
I'm not quite sure of your data layout from your description (is column S on
the Data sheet numbers or month names or ??), but I found if I added a helper Sum column (W, in my example) it was easy to write a SumProduct formula that works: =SUMPRODUCT(--(Data!$K$3:$K$241=Summary!A8),--(Data!$S$3:$S$241=Summary!C5),Data!$W$3:$W$241) You will need to adjust the cell references. If you can describe your data layout in more detail, I will be glad to help. Hope this helps, Hutch "phonemanager" wrote: I am trying to create a summary of data contained in a second worksheet in a workbook. I need to sum the dollar amouts of Columns S2:V241 but only if the amounts appear in a designated month under the supervisor's name. S5= January (on Data tab), C5= January (on Summary tab) K3:K241 = Supervisor's name (on Data tab), A8:A24 = Supervisor's name (on Summary tab) I have been playing with sumproduct formulas for 2 days now (lol) but just don't have enough experience with this type of formula and can't seem to make it work. Any help/suggestions woud be greatly appreciated! Thank you in advance! -- Will this ever be easy?? |
Need a formula to sum with conditions
Thank you for the response!
On the Data tab: Column S,T,U and V are numbers (dollar amounts) rows 3:241 Cell S1 is the month Column K3:K241 is the Name On the Summary tab: Column A8:A24 is Name Cell C5 is the Month Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for each month for the year. I hope that is clearer and thank you again. - Will this ever be easy?? "Tom Hutchins" wrote: I'm not quite sure of your data layout from your description (is column S on the Data sheet numbers or month names or ??), but I found if I added a helper Sum column (W, in my example) it was easy to write a SumProduct formula that works: =SUMPRODUCT(--(Data!$K$3:$K$241=Summary!A8),--(Data!$S$3:$S$241=Summary!C5),Data!$W$3:$W$241) You will need to adjust the cell references. If you can describe your data layout in more detail, I will be glad to help. Hope this helps, Hutch "phonemanager" wrote: I am trying to create a summary of data contained in a second worksheet in a workbook. I need to sum the dollar amouts of Columns S2:V241 but only if the amounts appear in a designated month under the supervisor's name. S5= January (on Data tab), C5= January (on Summary tab) K3:K241 = Supervisor's name (on Data tab), A8:A24 = Supervisor's name (on Summary tab) I have been playing with sumproduct formulas for 2 days now (lol) but just don't have enough experience with this type of formula and can't seem to make it work. Any help/suggestions woud be greatly appreciated! Thank you in advance! -- Will this ever be easy?? |
Need a formula to sum with conditions
Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for
each month for the year. From the layout as described, month is only in cell S1 on the Data sheet and C5 on the Summary sheet. Is this workbook for one month only (a new workbook every month?) Or, is this YTD data and each month is identified somewhere? Hutch "phonemanager" wrote: Thank you for the response! On the Data tab: Column S,T,U and V are numbers (dollar amounts) rows 3:241 Cell S1 is the month Column K3:K241 is the Name On the Summary tab: Column A8:A24 is Name Cell C5 is the Month Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for each month for the year. I hope that is clearer and thank you again. - Will this ever be easy?? "Tom Hutchins" wrote: I'm not quite sure of your data layout from your description (is column S on the Data sheet numbers or month names or ??), but I found if I added a helper Sum column (W, in my example) it was easy to write a SumProduct formula that works: =SUMPRODUCT(--(Data!$K$3:$K$241=Summary!A8),--(Data!$S$3:$S$241=Summary!C5),Data!$W$3:$W$241) You will need to adjust the cell references. If you can describe your data layout in more detail, I will be glad to help. Hope this helps, Hutch "phonemanager" wrote: I am trying to create a summary of data contained in a second worksheet in a workbook. I need to sum the dollar amouts of Columns S2:V241 but only if the amounts appear in a designated month under the supervisor's name. S5= January (on Data tab), C5= January (on Summary tab) K3:K241 = Supervisor's name (on Data tab), A8:A24 = Supervisor's name (on Summary tab) I have been playing with sumproduct formulas for 2 days now (lol) but just don't have enough experience with this type of formula and can't seem to make it work. Any help/suggestions woud be greatly appreciated! Thank you in advance! -- Will this ever be easy?? |
Need a formula to sum with conditions
On the Data sheet:
S1=January, W1=February, AA1=March On Summary sheet: C5=January, D5=February, E5= March, etc. -- Will this ever be easy?? "Tom Hutchins" wrote: Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for each month for the year. From the layout as described, month is only in cell S1 on the Data sheet and C5 on the Summary sheet. Is this workbook for one month only (a new workbook every month?) Or, is this YTD data and each month is identified somewhere? Hutch "phonemanager" wrote: Thank you for the response! On the Data tab: Column S,T,U and V are numbers (dollar amounts) rows 3:241 Cell S1 is the month Column K3:K241 is the Name On the Summary tab: Column A8:A24 is Name Cell C5 is the Month Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for each month for the year. I hope that is clearer and thank you again. - Will this ever be easy?? "Tom Hutchins" wrote: I'm not quite sure of your data layout from your description (is column S on the Data sheet numbers or month names or ??), but I found if I added a helper Sum column (W, in my example) it was easy to write a SumProduct formula that works: =SUMPRODUCT(--(Data!$K$3:$K$241=Summary!A8),--(Data!$S$3:$S$241=Summary!C5),Data!$W$3:$W$241) You will need to adjust the cell references. If you can describe your data layout in more detail, I will be glad to help. Hope this helps, Hutch "phonemanager" wrote: I am trying to create a summary of data contained in a second worksheet in a workbook. I need to sum the dollar amouts of Columns S2:V241 but only if the amounts appear in a designated month under the supervisor's name. S5= January (on Data tab), C5= January (on Summary tab) K3:K241 = Supervisor's name (on Data tab), A8:A24 = Supervisor's name (on Summary tab) I have been playing with sumproduct formulas for 2 days now (lol) but just don't have enough experience with this type of formula and can't seem to make it work. Any help/suggestions woud be greatly appreciated! Thank you in advance! -- Will this ever be easy?? |
Need a formula to sum with conditions
Okay the simple formula is
=SUMPRODUCT(--(Data!$K$3:$K$241=Summary!$A8),(Data!S$3:Data!S$24 1)+(Data!T$3:Data!T$241)+(Data!U$3:Data!U$241)+(Da ta!V$3:Data!V$241)) You can enter this in C8 on the Summary sheet and copy it down through C24 to get all of January. As you copy it accross to the other months, you will have to edit the column references (S W, T X,e tc.). Or, you could try the long formula: =SUMPRODUCT(--(Data!$K$3:$K$241=Summary!$A29),(INDIRECT("Data!"& ADDRESS(3,MATCH(C$5,Data!$1:$1,0))):INDIRECT("Data !"&ADDRESS(241,MATCH(C$5,Data!$1:$1,0))))+(INDIREC T("Data!"&ADDRESS(3,MATCH(C$5,Data!$1:$1,0)+1)):IN DIRECT("Data!"&ADDRESS(241,MATCH(C$5,Data!$1:$1,0) +1)))+(INDIRECT("Data!"&ADDRESS(3,MATCH(C$5,Data!$ 1:$1,0)+2)):INDIRECT("Data!"&ADDRESS(241,MATCH(C$5 ,Data!$1:$1,0)+2)))+(INDIRECT("Data!"&ADDRESS(3,MA TCH(C$5,Data!$1:$1,0)+3)):INDIRECT("Data!"&ADDRESS (241,MATCH(C$5,Data!$1:$1,0)+3)))) You should be able to enter this into C8 on the SUmmary sheet and copy it down & across with no modifications. Hope this helps, Hutch "phonemanager" wrote: On the Data sheet: S1=January, W1=February, AA1=March On Summary sheet: C5=January, D5=February, E5= March, etc. -- Will this ever be easy?? "Tom Hutchins" wrote: Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for each month for the year. From the layout as described, month is only in cell S1 on the Data sheet and C5 on the Summary sheet. Is this workbook for one month only (a new workbook every month?) Or, is this YTD data and each month is identified somewhere? Hutch "phonemanager" wrote: Thank you for the response! On the Data tab: Column S,T,U and V are numbers (dollar amounts) rows 3:241 Cell S1 is the month Column K3:K241 is the Name On the Summary tab: Column A8:A24 is Name Cell C5 is the Month Trying to sum Data tab S3:241, T3:241, U3:241 and V3:241 for each Name for each month for the year. I hope that is clearer and thank you again. - Will this ever be easy?? "Tom Hutchins" wrote: I'm not quite sure of your data layout from your description (is column S on the Data sheet numbers or month names or ??), but I found if I added a helper Sum column (W, in my example) it was easy to write a SumProduct formula that works: =SUMPRODUCT(--(Data!$K$3:$K$241=Summary!A8),--(Data!$S$3:$S$241=Summary!C5),Data!$W$3:$W$241) You will need to adjust the cell references. If you can describe your data layout in more detail, I will be glad to help. Hope this helps, Hutch "phonemanager" wrote: I am trying to create a summary of data contained in a second worksheet in a workbook. I need to sum the dollar amouts of Columns S2:V241 but only if the amounts appear in a designated month under the supervisor's name. S5= January (on Data tab), C5= January (on Summary tab) K3:K241 = Supervisor's name (on Data tab), A8:A24 = Supervisor's name (on Summary tab) I have been playing with sumproduct formulas for 2 days now (lol) but just don't have enough experience with this type of formula and can't seem to make it work. Any help/suggestions woud be greatly appreciated! Thank you in advance! -- Will this ever be easy?? |
Need a formula to sum with conditions
|
| All times are GMT +1. The time now is 12:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com