Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT/HLOOKUP Query
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!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT/HLOOKUP Query
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!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT/HLOOKUP Query
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!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT/HLOOKUP Query
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!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT/HLOOKUP Query
=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!!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT/HLOOKUP Query
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!!! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT/HLOOKUP Query
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems importing from an Access query | Excel Discussion (Misc queries) | |||
changing query source | Excel Worksheet Functions | |||
AHHH! Again | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
"Query cannot be edited by the Query Wizard" | Excel Discussion (Misc queries) |