Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
named range refers to: in a chart | Excel Discussion (Misc queries) | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions | |||
range name | Excel Worksheet Functions | |||
Average Range | Excel Worksheet Functions |