Can I sum or average a range with more than 1 condition?
 

I want to get an average based on multiple criteria
without having to sort the records and identify the range.
Of course I can get to the average if I can get the sum.

I'm aware that I can sum records within a range that meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2) *
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,IF
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula for
multple criteria. Any help out there?
THanks
BobT

BobT,

You could try a formula that does Sumif/Countif and count for the same
criteria that you sum for. For Example
=sumif(A1:A100,"X",C1:C100)/countif(A1:A100,"X")

Andrew


"BobT" wrote:

I want to get an average based on multiple criteria
without having to sort the records and identify the range.
Of course I can get to the average if I can get the sum.

I'm aware that I can sum records within a range that meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2) *
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,IF
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula for
multple criteria. Any help out there?
THanks
BobT

You're close -- SUMPRODUCT does the job, but you've left off the final
piece:
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2) *(Range3=Criteria3)*Range4
)

"BobT" wrote in message
...
I want to get an average based on multiple criteria
without having to sort the records and identify the range.
Of course I can get to the average if I can get the sum.

I'm aware that I can sum records within a range that meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2) *
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,IF
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula for
multple criteria. Any help out there?
THanks
BobT

Sorry about that last answer. I took a quick look at your questions and
didn't realize you were asking about averaging based on multiple criteria.
Don't think my solution will work for you.
Andrew

"BobT" wrote:

I want to get an average based on multiple criteria
without having to sort the records and identify the range.
Of course I can get to the average if I can get the sum.

I'm aware that I can sum records within a range that meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2) *
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,IF
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula for
multple criteria. Any help out there?
THanks
BobT

This does the job, Thanks

-----Original Message-----
You're close -- SUMPRODUCT does the job, but you've left
off the final
piece:
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2 )*
(Range3=Criteria3)*Range4
)

"BobT" wrote in message
...
I want to get an average based on multiple criteria
without having to sort the records and identify the
range.
Of course I can get to the average if I can get the sum.

I'm aware that I can sum records within a range that
meet
a codition outside the range using the SUMIF function
"=SUMIF(Range,criteria,sum range)"

I can also count records that meet multiple criteria
using
the SUMPRODUCT function
"=SUMPRODUCT((Range1=Criteria1)*(Range2=Criteria2) *
(Range3=Criteria3)"

I've also seen this array formula to count records that
meet multiple criteria:
{=SUM(IF(Range1=Criteria1,IF(Range2=Criteria2,IF
(Range3=Criteria3,1,0)))
But the SUMPRODUCT formula is easier to work with.

However, I have not found or figured out a sum formula
for
multple criteria. Any help out there?
THanks
BobT




.