View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_] Ashish Mathur[_2_] is offline
external usenet poster
 
Posts: 1,766
Default help with complicated formula

Hi,

Try this.

=SUMPRODUCT((A12:I350=B16)*I12:J350)/COUNTIF(A12:I350,B16), where B16 holds
Pleasanton.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

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