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