Average using lookup function

[psowerby]

I have 3 seperate ranges and would like to have an average from 3
columns depending on whether or not a specific product has been entered
in the following cells.

B6:B19
B27:B40
H6:H19

If one of the above cells="My Product" then I would like an average of
the following ranges

C6:C19
C27:C40
I6:I19

Can anyone give me a simple formula

Pete

 

[Toppers]

Pete,

Try:

A1 contains your match product:

=IF((COUNTIF(B6:B19,A1)),AVERAGE(C6:C19),"")

Enter using Control-Shift-Enter as this is an array formula

HTH

 

[Bob Phillips]

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"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[joeu2004]

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.

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?

 

[joeu2004]

[Given ....]
B6:B19
B27:B40
H6:H19

If one of the above cells="My Product" then I would like an average of
the following ranges
C6:C19
C27:C40
I6:I19

Do you really mean: average the values in {C9:C19, C27:C40, I6:I19}
corresponding to the cells in {B6:B19, B27:B40, H6:H19} that match "My
Product"?

I suspect so. But that is a very different specification than what you
wrote. If that is what you want, see Bob Phillips's answer. But for
what you wrote, the following might suffice (although I suspect there
is a more elegant solution):

=if( and( iserror(match("My Product", B6:B19, 0)),
iserror(match("My Product", B27:B40, 0)),
iserror(match("My Product", H6:H19, 0)) ),
"", average(C6:C19, C27:C40, I6:I19) )

Thus, if "My Product" is not in any one of the cells in {B6:B19,
B27:B40, H6:H19}, return "" (blank cell); otherwise, return the average
of the values in {C6:C19, C27:C40, I6:I19}.

 

[Bob Phillips]

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

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.

 

[joeu2004]

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"))

[....]
Why do we need the extra SUM(....) around SUMIF and COUNTIF?

Let me take a stab at answering my own question. SUMIF seems to be a
variant-type function, and the INDIRECT list seems to cause SUMIF to
return an array of 3 sums. This is evident when we select 3 columns
and enter SUMIF as an array formula (ctrl-shift-Enter). Thus, SUM is
needed to add the elements of the array of SUMIF results.

This is not be self-evident to me, based on the MS help text. Can
anyone point to a(n MS) reference that explains that behavior?

Moreover, it is not obvious to me that INDIRECT can be used to
construct an array of noncontiguous ranges. Can anyone point to a(n
MS) reference that explains that, too?

I mean: how do people learn of these gems? It is difficult to keep
track of them as they are demonstrated one by one on occassion in
forums like this. It would be nice to find a single reference that
explains these Excel behaviors canonically -- the "K&R" of Excel.

 

[Bob Phillips]

I don't believe that there is a reference Joe, it is learnt behaviour by
seeing what others come up with, and playing with it to see where you can
take it. I may be wrong, but I doubt that anyone (even at MS) understands
the formula code in enough depth to be able to predict how they can be
extended, but we find out by playing, by saying ' ... what if I did this?'.

For instance, look at how we use SUMPRODUCT nowadays. Nowhere in any MS
documentation that I have seen is that mentioned, although I would venture
that by virtue of the promotion of these techniques by the likes of Peo
Sjoblom, Domenic, (the late) Frank Kabel, Toppers, Biff, and dare I say,
myself, the technique is now ubiquitous.

I tried something the other day when I gave a formula to count the most
frequent occurrences of a text value in a range,
=INDEX(rng,MODE(MATCH(rng,rng,0))). It worked fine with a full range, but
failed on a sparse range. By recalling a common solution to counting unique
values in a range, =SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&"")), I was able to
amend the formula to cater for sparse range,
=INDEX(rng,MODE(MATCH(rng&"",rng&"",0))). Experience, like most good things
in life it has to be worked at and earned.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

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"))

[....]
Why do we need the extra SUM(....) around SUMIF and COUNTIF?

Let me take a stab at answering my own question. SUMIF seems to be a
variant-type function, and the INDIRECT list seems to cause SUMIF to
return an array of 3 sums. This is evident when we select 3 columns
and enter SUMIF as an array formula (ctrl-shift-Enter). Thus, SUM is
needed to add the elements of the array of SUMIF results.

This is not be self-evident to me, based on the MS help text. Can
anyone point to a(n MS) reference that explains that behavior?

Moreover, it is not obvious to me that INDIRECT can be used to
construct an array of noncontiguous ranges. Can anyone point to a(n
MS) reference that explains that, too?

I mean: how do people learn of these gems? It is difficult to keep
track of them as they are demonstrated one by one on occassion in
forums like this. It would be nice to find a single reference that
explains these Excel behaviors canonically -- the "K&R" of Excel.

 

[Toppers]

Bob,
Thanks for the mention in "despatches" but I certainly wouldn't
place myself in the same company as yourself and the others mentioned.

Thanks to you and many others, I am (very!) slowly improving my knowledge
and use of formulae but the ingenuity of experts like yourself always
astounds (and often baffles!) me.

There is a market for somone who could distill all the knowledge into a
single reference but equally the web sites frequently referred to in the NGs
are a fantastic source of material.

So many thanks to you and your fellow gurus - we need you!

I don't believe that there is a reference Joe, it is learnt behaviour by
seeing what others come up with, and playing with it to see where you can
take it. I may be wrong, but I doubt that anyone (even at MS) understands
the formula code in enough depth to be able to predict how they can be
extended, but we find out by playing, by saying ' ... what if I did this?'.

For instance, look at how we use SUMPRODUCT nowadays. Nowhere in any MS
documentation that I have seen is that mentioned, although I would venture
that by virtue of the promotion of these techniques by the likes of Peo
Sjoblom, Domenic, (the late) Frank Kabel, Toppers, Biff, and dare I say,
myself, the technique is now ubiquitous.

I tried something the other day when I gave a formula to count the most
frequent occurrences of a text value in a range,
=INDEX(rng,MODE(MATCH(rng,rng,0))). It worked fine with a full range, but
failed on a sparse range. By recalling a common solution to counting unique
values in a range, =SUMPRODUCT((rng<>"")/COUNTIF(rng,rng&"")), I was able to
amend the formula to cater for sparse range,
=INDEX(rng,MODE(MATCH(rng&"",rng&"",0))). Experience, like most good things
in life it has to be worked at and earned.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

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"))
[....]
Why do we need the extra SUM(....) around SUMIF and COUNTIF?

Let me take a stab at answering my own question. SUMIF seems to be a
variant-type function, and the INDIRECT list seems to cause SUMIF to
return an array of 3 sums. This is evident when we select 3 columns
and enter SUMIF as an array formula (ctrl-shift-Enter). Thus, SUM is
needed to add the elements of the array of SUMIF results.

This is not be self-evident to me, based on the MS help text. Can
anyone point to a(n MS) reference that explains that behavior?

Moreover, it is not obvious to me that INDIRECT can be used to
construct an array of noncontiguous ranges. Can anyone point to a(n
MS) reference that explains that, too?

I mean: how do people learn of these gems? It is difficult to keep
track of them as they are demonstrated one by one on occassion in
forums like this. It would be nice to find a single reference that
explains these Excel behaviors canonically -- the "K&R" of Excel.

 

[Ragdyer]

<<<"SUMIF seems to be a variant-type function, and the INDIRECT list seems
to cause SUMIF to return an array of 3 sums.">>>

Indirect really has nothing to do with this Sumif (and also Countif)
behavior.

Try this:
=Sumif(A1:A10,{"Tom","Dick","Harry"},B1:B10)
And all you'll have returned are totals matching the first criteria.

Likewise:
=SUMIF(B1:B10,{">=4",">=6"})*{1,-1}
And you'll only have the results of the first calculation returned

However, if you select the formulas in the formula bar, and hit <F9>, you'll
see an array of 3 values for the first and 2 values for the second, denoting
the results of *each one* of the criteria or calculations.

Why doesn't Sumif total these on it's own? ? ?

The Sum added to the Sumif takes care of the situation:
=SUM(SUMIF(A1:A10,{"Tom","Dick","Harry"},B1:B10))
=SUM(SUMIF(B1:B10,{">=4",">=6"})*{1,-1})

The first time I ever saw this function combination in these NGs was maybe a
year to a year and a half ago.
And I saw it in a post by a *not* regular contributor to these NGs, but from
an OP making a guess.

I can however, be corrected by anyone on this point.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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"))

[....]
Why do we need the extra SUM(....) around SUMIF and COUNTIF?

Let me take a stab at answering my own question. SUMIF seems to be a
variant-type function, and the INDIRECT list seems to cause SUMIF to
return an array of 3 sums. This is evident when we select 3 columns
and enter SUMIF as an array formula (ctrl-shift-Enter). Thus, SUM is
needed to add the elements of the array of SUMIF results.

This is not be self-evident to me, based on the MS help text. Can
anyone point to a(n MS) reference that explains that behavior?

Moreover, it is not obvious to me that INDIRECT can be used to
construct an array of noncontiguous ranges. Can anyone point to a(n
MS) reference that explains that, too?

I mean: how do people learn of these gems? It is difficult to keep
track of them as they are demonstrated one by one on occassion in
forums like this. It would be nice to find a single reference that
explains these Excel behaviors canonically -- the "K&R" of Excel.