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

  • Thread starter Thread starter BobT
  • Start date Start date
B

BobT

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




.
Click to expand...
 
Back
Top