Average/Sumif based on several columns

M

mslabbe

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
 
B

Bob Phillips

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

mslabbe

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
 
B

Bob Phillips

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)
 
Top