Can I get the mode, min, and max with multiple criteria?

[BobT]

Thanks again for the previous response.
Is there a way to get the mode min and or max with
multiple criteria?



-----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" <[email protected]> wrote in message
>news:[email protected]...

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

[AlfD]

Hi!

Not a good idea to start another thread here. Where's the original, without which none of this makes sense?

Alf

[Biff]

Hi!

All are array formulas, enter with the key combo of
CTRL,SHIFT,ENTER:

=MODE(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1:D10))))

=MIN(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1:D10))))

=MAX(IF(A1:A10="x",IF(B1:B10="y",IF(C1:C10="z",D1:D10))))

Biff

>-----Original Message-----
>
>Thanks again for the previous response.
>Is there a way to get the mode min and or max with
>multiple criteria?
>
>
>
>-----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" <[email protected]> wrote in message
>>news:[email protected]...
>
>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
>
>.
>