How do I get an average sale if multiple columns?

[Todd]

Column I J N
11 DP 3100
21 AF 10000
41 AF 8000
21 DP 12000
How do I get the average sale if column N=sale amount if I=11 and J=DP or if
I=21 J=AF Average Sale=? Please help me.
Thanks
Todd

 

[Biff]

Hi!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

How to enter an array formula:

http://cpearson.com/excel/array.htm

=AVERAGE(IF((I1:I4=11)*(J1:J4="DP"),N1:N4))

Better to use cells to hold the criteria:

A1 = 11
B1 = DP

=AVERAGE(IF((I1:I4=A1)*(J1:J4=B1),N1:N4))

Biff

 

[Todd]

Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the response

 

[Biff]

Do you have #N/A's in any of the ranges?

Biff

Hi Biff. Tried it and cell shows "#N/A". Thoughts? Thanks for the
response

 

[Todd]

This is a multi worksheet workbook. It is a lead and sales tracking
workbook. It does not contain any #N/A's. I have to show the average sale
by salesperson by month & for the year. Here is a better way of putting it.
Column I J N
11 DP $1,300
12 JJ $2,000
21 DP $12,000
21 AF $20,000
41 AF $8,000
11 DP $1,500
If Column I = 11 and J = DP, what is the average sale (column n). For my
month of july I start @ row 217 and end @ 300. The formula the way I entered
it is =AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3),S!N1:N4)). The
'S'!(S!) represents a worksheet (The S stands for Sales). Is this more
helpful? All thanks
Todd

 

[Biff]

=AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3),S!N1:N4))

The size of the ranges MUST be exactly the same:

N1:N4 is not the same size as I217:I300 and J217:J300

Maybe you meant:

=AVERAGE(IF((S!I217:I300=S!V3)*(S!J217:J300=S!U3),S!N217:N300))

Biff

 

[Todd]

Biff,
For that formula i am getting 1247.45. The totals that equal that equation
are, (6866,6597,5084,6025,6589,27826,2632=61619 /7=8,802.71) Obviously, there
is something not working. I am hitting the Ctrl-Shift-Enter for the array
and all of my formulas now are exactly the same (thanks for noticing that
though). I am definetly scratching my head on this one. Thanks for your
help so far.
Todd

 

[Biff]

Can you send me a copy of the file?

If so, just let me know how to contact you.

Biff

 

[Todd]

(e-mail address removed) is my e-mail
Thanks

Can you send me a copy of the file?

If so, just let me know how to contact you.

Biff

 

[Biff]

Ok, sent an email.

Biff

(e-mail address removed) is my e-mail
Thanks