SUMPRODUCT/HLOOKUP Query

G

Guest

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

Guest

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

Guest

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
 
G

Guest

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

Bob Phillips

=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)
 
G

Guest

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

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top