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

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
>

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
> >

Try:

=SUMPRODUCT((B1:F1="Assy")*(A2:A9="Inspection")*(B2: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
> > >

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")*(B2: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
> > > >

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")*(B2: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
> > > > >