Can I use AVERAGEIFS?

  • Thread starter Thread starter JRD
  • Start date Start date
Try (in 2007)
=AVERAGEIFS(D1:D10,A1:A10,">=" & DATE(2009,10,1),A1:A10,
"<" & DATE(2009,11,1),B1:B10,"Reported",C1:C10,"*John*")

array formula which will work for 2003/2007

=AVERAGE(IF(TEXT(A1:A10,"mmyyyy")="102009",IF(B1:B10="Reported",
IF(ISNUMBER(SEARCH("John",C1:C10)),IF(ISNUMBER(D1:D10),D1:D10)))))

If this post helps click Yes
 
T. Valko said:
Can't use AND in this application.
Click to expand...

Thanks. My bad! For i = 1 to 10000: Debug.Print "I will always test my
solutions": Next.

I should have written:

=AVERAGE(IF((MONTH(A1:A6)=10)*(B1:B6="reported")*ISNUMBER(SEARCH("john",C1:C6)),
D1:D6))

Again, that's an array formula. See the notes in my original posting.

Also note that this assumes that A1:A6 contains actual dates (serial
numbers), formatted as d/mm/yyyy.

the answer for the example would be 4+2
divided by 2 = 3
Click to expand...

I don't think so. The only lines that meet all 3 conditions are lines 4 and
6. The average is (2+2)/2 = 2.


----- original message -----
 
Back
Top