| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of columns that vary in position
I need to sum up the total of all numbers in columns titled Assy. These
columns will vary in position in the spreadsheet. example of data Fabs Assy Fabs Assy Assy Expediting - Shortages 0 0 0 0 0 Inspection 0 48 0 0 0 Housekeeping 26.6 49.6 8 0 0 Maintenance 0 0 0 0 0 Rework 0 5.9 0 115 0 Waiting 0 0 0 73 0 Communications 26.6 47.3 0 41.5 0 Training 12 40.5 37 0 209 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of columns that vary in position
Try (based on your supplied table):
=SUMPRODUCT((B1:F1="Assy")*(B2:F9)) HTH "gibsol" wrote: I need to sum up the total of all numbers in columns titled Assy. These columns will vary in position in the spreadsheet. example of data Fabs Assy Fabs Assy Assy Expediting - Shortages 0 0 0 0 0 Inspection 0 48 0 0 0 Housekeeping 26.6 49.6 8 0 0 Maintenance 0 0 0 0 0 Rework 0 5.9 0 115 0 Waiting 0 0 0 73 0 Communications 26.6 47.3 0 41.5 0 Training 12 40.5 37 0 209 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of columns that vary in position
Thanks toppers that worked a treat.
Do you have an idea how I would then set it to sum up for inspection only. Would I use Sumif. ? "Toppers" wrote: Try (based on your supplied table): =SUMPRODUCT((B1:F1="Assy")*(B2:F9)) HTH "gibsol" wrote: I need to sum up the total of all numbers in columns titled Assy. These columns will vary in position in the spreadsheet. example of data Fabs Assy Fabs Assy Assy Expediting - Shortages 0 0 0 0 0 Inspection 0 48 0 0 0 Housekeeping 26.6 49.6 8 0 0 Maintenance 0 0 0 0 0 Rework 0 5.9 0 115 0 Waiting 0 0 0 73 0 Communications 26.6 47.3 0 41.5 0 Training 12 40.5 37 0 209 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of columns that vary in position
Try:
=SUMPRODUCT((B1:F1="Assy")*(A2:A9="Inspection")*(B 2:F9)) HTH "gibsol" wrote: Thanks toppers that worked a treat. Do you have an idea how I would then set it to sum up for inspection only. Would I use Sumif. ? "Toppers" wrote: Try (based on your supplied table): =SUMPRODUCT((B1:F1="Assy")*(B2:F9)) HTH "gibsol" wrote: I need to sum up the total of all numbers in columns titled Assy. These columns will vary in position in the spreadsheet. example of data Fabs Assy Fabs Assy Assy Expediting - Shortages 0 0 0 0 0 Inspection 0 48 0 0 0 Housekeeping 26.6 49.6 8 0 0 Maintenance 0 0 0 0 0 Rework 0 5.9 0 115 0 Waiting 0 0 0 73 0 Communications 26.6 47.3 0 41.5 0 Training 12 40.5 37 0 209 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of columns that vary in position
Superb, works great, can get back to normal life now instead of staring at
this report. "Toppers" wrote: Try: =SUMPRODUCT((B1:F1="Assy")*(A2:A9="Inspection")*(B 2:F9)) HTH "gibsol" wrote: Thanks toppers that worked a treat. Do you have an idea how I would then set it to sum up for inspection only. Would I use Sumif. ? "Toppers" wrote: Try (based on your supplied table): =SUMPRODUCT((B1:F1="Assy")*(B2:F9)) HTH "gibsol" wrote: I need to sum up the total of all numbers in columns titled Assy. These columns will vary in position in the spreadsheet. example of data Fabs Assy Fabs Assy Assy Expediting - Shortages 0 0 0 0 0 Inspection 0 48 0 0 0 Housekeeping 26.6 49.6 8 0 0 Maintenance 0 0 0 0 0 Rework 0 5.9 0 115 0 Waiting 0 0 0 73 0 Communications 26.6 47.3 0 41.5 0 Training 12 40.5 37 0 209 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of columns that vary in position
Thanks for the feedback.
"gibsol" wrote: Superb, works great, can get back to normal life now instead of staring at this report. "Toppers" wrote: Try: =SUMPRODUCT((B1:F1="Assy")*(A2:A9="Inspection")*(B 2:F9)) HTH "gibsol" wrote: Thanks toppers that worked a treat. Do you have an idea how I would then set it to sum up for inspection only. Would I use Sumif. ? "Toppers" wrote: Try (based on your supplied table): =SUMPRODUCT((B1:F1="Assy")*(B2:F9)) HTH "gibsol" wrote: I need to sum up the total of all numbers in columns titled Assy. These columns will vary in position in the spreadsheet. example of data Fabs Assy Fabs Assy Assy Expediting - Shortages 0 0 0 0 0 Inspection 0 48 0 0 0 Housekeeping 26.6 49.6 8 0 0 Maintenance 0 0 0 0 0 Rework 0 5.9 0 115 0 Waiting 0 0 0 73 0 Communications 26.6 47.3 0 41.5 0 Training 12 40.5 37 0 209 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I make excel hide columns when it says cannot move objects | Excel Discussion (Misc queries) | |||
| How do I sort the data in 8 columns by two of the columns? | Excel Worksheet Functions | |||
| How to swap rows and columns? | Excel Discussion (Misc queries) | |||
| Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Hybrid Mode
