AVERAGEIF problem

  • Thread starter Thread starter Jay Gustafson
  • Start date Start date
J

Jay Gustafson

Hello,

I'm having a bit of trouble with this formula below, can someone let me know what I'm doing wrong? The formula does work correctly when I use SUMIF or COUNTIF, but when I try to use AVERAGEIF it returns ?name.

=AVERAGEIF($H$3:$H$23,C11,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C12,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C13,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C14,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C15,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C16,$K$3:$K$23)

Thanks,
Jay
 
Jay,

There is no AVERAGEIF function. Instead, try something like the
following array formula.

=AVERAGE(IF($H$3:$H$23=C11,$K$3:$K$23,FALSE))


Since this is an array formula, you must press Ctrl+Shift+Enter
rather than just Enter when you first type in the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message Hello,

I'm having a bit of trouble with this formula below, can someone
let me know what I'm doing wrong? The formula does work
correctly when I use SUMIF or COUNTIF, but when I try to use
AVERAGEIF it returns ?name.

=AVERAGEIF($H$3:$H$23,C11,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C12,$K
$3:$K$23)+AVERAGEIF($H$3:$H$23,C13,$K$3:$K$23)+AVERAGEIF($H$3:$H$
23,C14,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C15,$K$3:$K$23)+AVERAGEIF
($H$3:$H$23,C16,$K$3:$K$23)

Thanks,
Jay
 
Hi
there's no aVERAGEIF function. Though you can try the following array
formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF($H$3:$H$23=C11,$K$3:$K$23))+.....

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag Hello,

I'm having a bit of trouble with this formula below, can someone let me
know what I'm doing wrong? The formula does work correctly when I use
SUMIF or COUNTIF, but when I try to use AVERAGEIF it returns ?name.

=AVERAGEIF($H$3:$H$23,C11,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C12,$K$3:$K$
23)+AVERAGEIF($H$3:$H$23,C13,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C14,$K$3:
$K$23)+AVERAGEIF($H$3:$H$23,C15,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C16,$K
$3:$K$23)

Thanks,
Jay
 
Actually,

Your solution worked, but my setup is wrong. Technically, I want all those
values summed, but averaged at the end. Does that make sense?
 
Hi Jay

No wonder, you're having trouble. The AVERAGEIF function
doesn't exist. Instead try this array formula:

=SUM(IF(COUNTIF(H3:H23,C11:C16),SUMIF(H3:H23,C11:C16,K3:K23)/COUNTIF(H3:H23,
C11:C16)))

The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Jay Gustafson" <[email protected]> skrev i en meddelelse
Hello,

I'm having a bit of trouble with this formula below, can someone let me know
what I'm doing wrong? The formula does work correctly when I use SUMIF or
COUNTIF, but when I try to use AVERAGEIF it returns ?name.

=AVERAGEIF($H$3:$H$23,C11,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C12,$K$3:$K$23)+A
VERAGEIF($H$3:$H$23,C13,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C14,$K$3:$K$23)+AVE
RAGEIF($H$3:$H$23,C15,$K$3:$K$23)+AVERAGEIF($H$3:$H$23,C16,$K$3:$K$23)

Thanks,
Jay
 
If I have understood you properly, then this
array formula will do the job:

=SUM(IF(COUNTIF(H3:H23,C11:C16),SUMIF(H3:H23,C11:C16,K3:K23)))/
SUM(COUNTIF(H3:H23,C11:C16))

again to be entered with <Shift><Ctrl><Enter>
 
First, Please stay in the ORIGINAL thread. Try this idea. Tested with A in
c11 and B in c12

Sub AverageIFmulti()
For Each c In Range("c11:c12") 'Selection
ms = ms & "," & """" & c & """"
Next
ms = "{" & Right(ms, Len(ms) - 1) & "}"
MsgBox ms
Range("c1").Formula = _
"=sumproduct((h3:h23=" & ms & ")*k3:k23)/sumproduct((h3:h23=" & ms & ")*1)"
End Sub
 
Back
Top