Sumproduct based on certain criteria

[Karthik]

Hi All,
I have data with 6 columns and more than 2500 rows. I want to find the
average of column "F" if all the other columns fulfill certain conditions.

Auto AAAC22 John 2 3 45.62
Auto AAAC22 John 2 3 13.1
Auto AAAC24 Kelly 6 3 14.5
Auto BAAC25 John 2 1 15
Manual AAAC26 John 2 3 15.24
Auto AXAC22 John 5 3 45.62
Auto AAAC28 Kelly 1 1 15.55
Manual AAAC22 John 2 3 45.62
Auto AAAC22 John 2 3 16.72

I would like to know a formula that calculates the average of 'F' if A=Auto,
B=Starts with letter 'A' and C=John.

I tried the formual
=sumproduct(--(a$2:a$9999="auto"),--(B$2:B$9999="A?????")--(C$2:C$9999="John"),f$2:F$9999).
but its not working. Could you please help me on this formula..

Thanks in advance

 

[Karthik]

Hi All,

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF((A2:A10="auto")*(LEFT(B2:B10)={"A","B"})*(C2:C10="John"),F2:F10))

Better to use cells to hold the criteria:

H2 = auto
I2 = A
J2 = B
K2 = John

=AVERAGE(IF((A2:A10=H2)*(LEFT(B2:B10)=I2:J2)*(C2:C10=K2),F2:F10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

You might want to consider using AutoFilter and SUBTOTAL.

 

[Per Jessen]

Hi
Use an unused column as helper column to extract the first letter in
Col B.

In this example I use column G as helper column.

Enter this formula in G2 and copy down:

=Left(B2,1)

Now use this formula to calculate the average:

=SUMPRODUCT(--(A$2:A$9999="Auto"),--(G$2:G$9999="A"),--(C$2:C
$9999="John"),F$2:F$9999)

Hopes this helps