I have a spreadsheet that records information weekly. I'm looking to set up
another spreadsheet that takes the information from this one and inputs
themothly totals/averages (depending on the data).

My first spreadsheet has Row 1 showing Jan Jan Jan Jan Feb Feb Feb Feb Feb
Mar etc (the number of time the month shows depends on the number of weeks in
that month).

Row 2 has the week number ie 1 2 3 4 5 6 7 8 etc.

Column A has various headings (the first being number of hours)

I want my new spreadsheet to count the sum of hours relating to the month of
Jan. I'm assuming for this I need to use HLOOKUP as the data is stored
horizontally. So far I can only get HLOOKUP to return the data from the first
time it sees Jan in the row.

Can anyone assist me with a formula using SUMPRODUCT combined with HLOOKUP
to calculate all of the times it sees Jan?

Any other ideas would also be greatly apreciated!!!

If the hours are in row 3 and the data extends through column AZ:
=sumproduct(--($A$1:$AZ$1="Jan"),($A$3:$AZ$3))
A different approach would be to include a row with the start date (or end
date, whichever you'r using to assign a week to a particular month), and then
use two sumif functions. Ex if the date is in row 3 and the hours still in
row 4, then something like =sumif($3:$3,"=" & date(2006,1,1),$4:$4) -
sumif($3:$3,"=" & date(2006,2,1),$4:$4). The first part adds all the hours
in weeks beginning 1/1/06 or later; the second part subtracts all the hours
in weeks beginning 2/1/06 or later. What's left is only the hours from weeks
beginning in January.

"luvthavodka" wrote:

I have a spreadsheet that records information weekly. I'm looking to set up
another spreadsheet that takes the information from this one and inputs
themothly totals/averages (depending on the data).

My first spreadsheet has Row 1 showing Jan Jan Jan Jan Feb Feb Feb Feb Feb
Mar etc (the number of time the month shows depends on the number of weeks in
that month).

Row 2 has the week number ie 1 2 3 4 5 6 7 8 etc.

Column A has various headings (the first being number of hours)

I want my new spreadsheet to count the sum of hours relating to the month of
Jan. I'm assuming for this I need to use HLOOKUP as the data is stored
horizontally. So far I can only get HLOOKUP to return the data from the first
time it sees Jan in the row.

Can anyone assist me with a formula using SUMPRODUCT combined with HLOOKUP
to calculate all of the times it sees Jan?

Any other ideas would also be greatly apreciated!!!

Er, sorry. I should have recommended the sumif even with the month labels
you've got. You'd only need sumproduct if there are multiple criteria. But
your sole criterion is the month (only issue you've had with lookup is that
the months appear multiple times). So with the month labels in row one and
hours in row 3, it's just =sumif($1:$1,"Jan",$3:$3).
--Bruce

"luvthavodka" wrote:

I have a spreadsheet that records information weekly. I'm looking to set up
another spreadsheet that takes the information from this one and inputs
themothly totals/averages (depending on the data).

My first spreadsheet has Row 1 showing Jan Jan Jan Jan Feb Feb Feb Feb Feb
Mar etc (the number of time the month shows depends on the number of weeks in
that month).

Row 2 has the week number ie 1 2 3 4 5 6 7 8 etc.

Column A has various headings (the first being number of hours)

I want my new spreadsheet to count the sum of hours relating to the month of
Jan. I'm assuming for this I need to use HLOOKUP as the data is stored
horizontally. So far I can only get HLOOKUP to return the data from the first
time it sees Jan in the row.

Can anyone assist me with a formula using SUMPRODUCT combined with HLOOKUP
to calculate all of the times it sees Jan?

Any other ideas would also be greatly apreciated!!!

Of course - so simple! I'm guilty of trying to overcomplicate things again!!!

I also need to work out averages for some of the data, rather than the
total. With some months being 4 weeks and some 5 I'd rather not use:

=(sumif($1:$1,"Jan",$3:$3))/4

=averageif($1:$1,"Jan",$3:$3) is what I'd like to do, but obviously average
if isn't a function. Do you know a way around this?

"bpeltzer" wrote:

Er, sorry. I should have recommended the sumif even with the month labels
you've got. You'd only need sumproduct if there are multiple criteria. But
your sole criterion is the month (only issue you've had with lookup is that
the months appear multiple times). So with the month labels in row one and
hours in row 3, it's just =sumif($1:$1,"Jan",$3:$3).
--Bruce

"luvthavodka" wrote:

I have a spreadsheet that records information weekly. I'm looking to set up
another spreadsheet that takes the information from this one and inputs
themothly totals/averages (depending on the data).

My first spreadsheet has Row 1 showing Jan Jan Jan Jan Feb Feb Feb Feb Feb
Mar etc (the number of time the month shows depends on the number of weeks in
that month).

Row 2 has the week number ie 1 2 3 4 5 6 7 8 etc.

Column A has various headings (the first being number of hours)

I want my new spreadsheet to count the sum of hours relating to the month of
Jan. I'm assuming for this I need to use HLOOKUP as the data is stored
horizontally. So far I can only get HLOOKUP to return the data from the first
time it sees Jan in the row.

Can anyone assist me with a formula using SUMPRODUCT combined with HLOOKUP
to calculate all of the times it sees Jan?

Any other ideas would also be greatly apreciated!!!

=average(if($1:$1,"Jan",$3:$3))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"luvthavodka" wrote in message
...
Of course - so simple! I'm guilty of trying to overcomplicate things
again!!!

I also need to work out averages for some of the data, rather than the
total. With some months being 4 weeks and some 5 I'd rather not use:

=(sumif($1:$1,"Jan",$3:$3))/4

=averageif($1:$1,"Jan",$3:$3) is what I'd like to do, but obviously
average
if isn't a function. Do you know a way around this?

"bpeltzer" wrote:

Er, sorry. I should have recommended the sumif even with the month
labels
you've got. You'd only need sumproduct if there are multiple criteria.
But
your sole criterion is the month (only issue you've had with lookup is
that
the months appear multiple times). So with the month labels in row one
and
hours in row 3, it's just =sumif($1:$1,"Jan",$3:$3).
--Bruce

"luvthavodka" wrote:

I have a spreadsheet that records information weekly. I'm looking to
set up
another spreadsheet that takes the information from this one and
inputs
themothly totals/averages (depending on the data).

My first spreadsheet has Row 1 showing Jan Jan Jan Jan Feb Feb Feb Feb
Feb
Mar etc (the number of time the month shows depends on the number of
weeks in
that month).

Row 2 has the week number ie 1 2 3 4 5 6 7 8 etc.

Column A has various headings (the first being number of hours)

I want my new spreadsheet to count the sum of hours relating to the
month of
Jan. I'm assuming for this I need to use HLOOKUP as the data is stored
horizontally. So far I can only get HLOOKUP to return the data from
the first
time it sees Jan in the row.

Can anyone assist me with a formula using SUMPRODUCT combined with
HLOOKUP
to calculate all of the times it sees Jan?

Any other ideas would also be greatly apreciated!!!

Thanks Bob, but this is returning #VALUE. I've entered asfollows

=AVERAGE(IF('Weekly Input Sht'!$3:$3,D$3,'Weekly Input Sht'!11:11))

Where Weekly Input Sht 3:3 is the row with months in, 11:11 is the row i
want the average from and D3 is January.

I committed with Ctrl-Shift-Enter but this still returns #VALUE.

"Bob Phillips" wrote:

=average(if($1:$1,"Jan",$3:$3))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"luvthavodka" wrote in message
...
Of course - so simple! I'm guilty of trying to overcomplicate things
again!!!

I also need to work out averages for some of the data, rather than the
total. With some months being 4 weeks and some 5 I'd rather not use:

=(sumif($1:$1,"Jan",$3:$3))/4

=averageif($1:$1,"Jan",$3:$3) is what I'd like to do, but obviously
average
if isn't a function. Do you know a way around this?

"bpeltzer" wrote:

Er, sorry. I should have recommended the sumif even with the month
labels
you've got. You'd only need sumproduct if there are multiple criteria.
But
your sole criterion is the month (only issue you've had with lookup is
that
the months appear multiple times). So with the month labels in row one
and
hours in row 3, it's just =sumif($1:$1,"Jan",$3:$3).
--Bruce

"luvthavodka" wrote:

I have a spreadsheet that records information weekly. I'm looking to
set up
another spreadsheet that takes the information from this one and
inputs
themothly totals/averages (depending on the data).

My first spreadsheet has Row 1 showing Jan Jan Jan Jan Feb Feb Feb Feb
Feb
Mar etc (the number of time the month shows depends on the number of
weeks in
that month).

Row 2 has the week number ie 1 2 3 4 5 6 7 8 etc.

Column A has various headings (the first being number of hours)

I want my new spreadsheet to count the sum of hours relating to the
month of
Jan. I'm assuming for this I need to use HLOOKUP as the data is stored
horizontally. So far I can only get HLOOKUP to return the data from
the first
time it sees Jan in the row.

Can anyone assist me with a formula using SUMPRODUCT combined with
HLOOKUP
to calculate all of the times it sees Jan?

Any other ideas would also be greatly apreciated!!!

Sorry, it should have been =

=AVERAGE(IF('Weekly Input Sht'!$3:$3=D$3,'Weekly Input Sht'!11:11))

still array-entered

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"luvthavodka" wrote in message
...
Thanks Bob, but this is returning #VALUE. I've entered asfollows

=AVERAGE(IF('Weekly Input Sht'!$3:$3,D$3,'Weekly Input Sht'!11:11))

Where Weekly Input Sht 3:3 is the row with months in, 11:11 is the row i
want the average from and D3 is January.

I committed with Ctrl-Shift-Enter but this still returns #VALUE.

"Bob Phillips" wrote:

=average(if($1:$1,"Jan",$3:$3))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"luvthavodka" wrote in message
...
Of course - so simple! I'm guilty of trying to overcomplicate things
again!!!

I also need to work out averages for some of the data, rather than the
total. With some months being 4 weeks and some 5 I'd rather not use:

=(sumif($1:$1,"Jan",$3:$3))/4

=averageif($1:$1,"Jan",$3:$3) is what I'd like to do, but obviously
average
if isn't a function. Do you know a way around this?

"bpeltzer" wrote:

Er, sorry. I should have recommended the sumif even with the month
labels
you've got. You'd only need sumproduct if there are multiple
criteria.
But
your sole criterion is the month (only issue you've had with lookup
is
that
the months appear multiple times). So with the month labels in row
one
and
hours in row 3, it's just =sumif($1:$1,"Jan",$3:$3).
--Bruce

"luvthavodka" wrote:

I have a spreadsheet that records information weekly. I'm looking
to
set up
another spreadsheet that takes the information from this one and
inputs
themothly totals/averages (depending on the data).

My first spreadsheet has Row 1 showing Jan Jan Jan Jan Feb Feb Feb
Feb
Feb
Mar etc (the number of time the month shows depends on the number
of
weeks in
that month).

Row 2 has the week number ie 1 2 3 4 5 6 7 8 etc.

Column A has various headings (the first being number of hours)

I want my new spreadsheet to count the sum of hours relating to
the
month of
Jan. I'm assuming for this I need to use HLOOKUP as the data is
stored
horizontally. So far I can only get HLOOKUP to return the data
from
the first
time it sees Jan in the row.

Can anyone assist me with a formula using SUMPRODUCT combined with
HLOOKUP
to calculate all of the times it sees Jan?

Any other ideas would also be greatly apreciated!!!