Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average/Sumif based on several columns
Hi...I've read several post based on what I'm looking for, but I haven't been
able to complete what I'm trying to do. I'm attempting to find the average on a column $I3:$I65536. This average is only if it equals items in range $F3:$F65536 and within a date range that I would like to be able to change from time to time...the date range is $B3:$B65536. I've been able to do it when there is exact match of two items, but have not seen a way to do it with the date range. The next formula should be easier...I'd like to SUMIF $K3:$K65536 based on the same parameters listed on the above average formula. Any thoughts would be greatly appreciated. Cheers |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average/Sumif based on several columns
=AVERAGE(IF(($B3:$B65536=--"2007-01-01")*($B3:$B65536<=--"2007-01-31")*(
$I3:$I65536= $F3:$F65536), $I3:$I65536)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. and =SUMPRODUCT(--($B3:$B65536=--"2007-01-01"),--($B3:$B65536<=--"2007-01-31"), --( $K3:$K65536= $F3:$F65536), $I3:$I65536) which is not -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "mslabbe" wrote in message ... Hi...I've read several post based on what I'm looking for, but I haven't been able to complete what I'm trying to do. I'm attempting to find the average on a column $I3:$I65536. This average is only if it equals items in range $F3:$F65536 and within a date range that I would like to be able to change from time to time...the date range is $B3:$B65536. I've been able to do it when there is exact match of two items, but have not seen a way to do it with the date range. The next formula should be easier...I'd like to SUMIF $K3:$K65536 based on the same parameters listed on the above average formula. Any thoughts would be greatly appreciated. Cheers |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average/Sumif based on several columns
Thanks Bob...could I have the date reference a cell, that I could change?
Cuz, this formula will be used several times and would like to be able to change the dates. Thanks "Bob Phillips" wrote: =AVERAGE(IF(($B3:$B65536=--"2007-01-01")*($B3:$B65536<=--"2007-01-31")*( $I3:$I65536= $F3:$F65536), $I3:$I65536)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. and =SUMPRODUCT(--($B3:$B65536=--"2007-01-01"),--($B3:$B65536<=--"2007-01-31"), --( $K3:$K65536= $F3:$F65536), $I3:$I65536) which is not -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "mslabbe" wrote in message ... Hi...I've read several post based on what I'm looking for, but I haven't been able to complete what I'm trying to do. I'm attempting to find the average on a column $I3:$I65536. This average is only if it equals items in range $F3:$F65536 and within a date range that I would like to be able to change from time to time...the date range is $B3:$B65536. I've been able to do it when there is exact match of two items, but have not seen a way to do it with the date range. The next formula should be easier...I'd like to SUMIF $K3:$K65536 based on the same parameters listed on the above average formula. Any thoughts would be greatly appreciated. Cheers |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average/Sumif based on several columns
yes the dates could be replaced by a cell reference, for
instance --"2007-0101" could be replaced by M1. -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "mslabbe" wrote in message ... Thanks Bob...could I have the date reference a cell, that I could change? Cuz, this formula will be used several times and would like to be able to change the dates. Thanks "Bob Phillips" wrote: =AVERAGE(IF(($B3:$B65536=--"2007-01-01")*($B3:$B65536<=--"2007-01-31")*( $I3:$I65536= $F3:$F65536), $I3:$I65536)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. and =SUMPRODUCT(--($B3:$B65536=--"2007-01-01"),--($B3:$B65536<=--"2007-01-31"), --( $K3:$K65536= $F3:$F65536), $I3:$I65536) which is not -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "mslabbe" wrote in message ... Hi...I've read several post based on what I'm looking for, but I haven't been able to complete what I'm trying to do. I'm attempting to find the average on a column $I3:$I65536. This average is only if it equals items in range $F3:$F65536 and within a date range that I would like to be able to change from time to time...the date range is $B3:$B65536. I've been able to do it when there is exact match of two items, but have not seen a way to do it with the date range. The next formula should be easier...I'd like to SUMIF $K3:$K65536 based on the same parameters listed on the above average formula. Any thoughts would be greatly appreciated. Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating values in two columns based on a variable | Excel Discussion (Misc queries) | |||
get a total based on criteria in two columns | Excel Worksheet Functions | |||
how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
Hiding columns based on user/password | Excel Worksheet Functions |