wrote in message
oups.com...
Bob Phillips wrote:
Maybe this is what you want
=SUM(SUMIF(INDIRECT({"B6:B19","B27:B40","H6:H19"}) ,"My
Product",INDIRECT({"C6:C19","C27:C40","I6:I19"})))/
SUM(COUNTIF(INDIRECT({"B6:B19","B27:B40","H6:H19"} ),"My Product"))

Great answer! I "knew" it was something "simple" like this, but I
didn't think of using INDIRECT to make it work. I think it only needs
to be noted that you are interpreting the OP to mean: average the
values in {C9:C19, C27:C40, I6:I19} corresponding to the values in
{B6:B19, B27:B40, H6:H19} that match "My Product". That is not what
the OP wrote; but I agree that that is probably what the OP meant.

I agree, the spec was a little ambiguous, so I made an assumption.
Unfortunately, as I am sure that you know, that is something that we (have
to) do very often in trying to give these solutions <g

One question.... Why do we need the extra SUM(....) around SUMIF and
COUNTIF? I know you are right, based on experiments. But I would have
thought that the SUMIF(...) and COUNTIF(...) alone would do the trick.
Is this simply an anomaly of Excel behavior? Or is there some good
reasoning for this behavior that escapes me?

The reason is that SUMIF and COUNTIF will not work directly with the array
of values (the non-contiguous ranges) that we are passing to SUMIF and
COUNTIF. By adding the SUM, that will, we effectively create multiple
SUMIF/COUNTIFs that get aggregated by SUM. I originally came up with this
technique using SUMPRODUCT, but someone pointed out to me that SUM does it
just as well, so I use that now.