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

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

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

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