ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Average/Sumif based on several columns (https://www.excelbanter.com/excel-worksheet-functions/129159-average-sumif-based-several-columns.html)

mslabbe

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

Bob Phillips

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




mslabbe

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





Bob Phillips

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








All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com