1. The reason you are getting a #Name error is you used AVERAGE inside of
Sumproduct. Excel thinks this is a named range, and therefore reports that
it is missing. You can't get Sumproduct to average this way. However, as
demonstrated, you can use Sumif/Countif.

2. What version of Excel are you using that doesn't support
Ctrl-Shift-Enter?

3. Your use of the range a12:i350 is most unusual. If you're trying to
average column I, why would it have "Pleasonton" in it?

4. If you still want to check the entire range, what do you want to happen
when the entries don't correspond? Suppose A12 has "Pleasonton", B12 has it
as well, but C12 doesn't. Do you want this counted, or not?

5. What happened when you tried my suggested formula?

6. Please top post your replies.

Regards,
Fred


"Chris" wrote in message
...
On Mar 11, 3:23 pm, "Fred Smith" wrote:
What results are you getting, and what results do you expect?

One thing that looks odd is the range for "Pleasanton". Should it be
A12:A350?

One way to replicate Averageif is Sumif/Countif, as in:
=sumif(a12:a350,"Pleasanton",i12:i350)/countif(a12:a350,"Pleasanton")+sumif*(a12:a350,"Pl easanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.

"Chris" wrote in message

...



I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?- Hide quoted text -

- Show quoted text -

Getting a #name? error. I wanted the entire range to be searched for
"pleasanton" hence the a12:I350, "Pleasanton"