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" wrote:

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

Biff

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

"Biff" wrote:

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" wrote in message
...
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