Thread: How do I get an average sale if multiple columns?

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

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" <Todd@discussions.microsoft.com> wrote in message
news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
> 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

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" <Todd@discussions.microsoft.com> wrote in message
> news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
> > 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
>
>
>

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

Biff

"Todd" <Todd@discussions.microsoft.com> wrote in message
news:2B43C961-C779-44BF-A4F2-EA8FACCBFF5B@microsoft.com...
> 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" <Todd@discussions.microsoft.com> wrote in message
>> news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
>> > 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
>>
>>
>>

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" <Todd@discussions.microsoft.com> wrote in message
> news:2B43C961-C779-44BF-A4F2-EA8FACCBFF5B@microsoft.com...
> > 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" <Todd@discussions.microsoft.com> wrote in message
> >> news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
> >> > 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
> >>
> >>
> >>
>
>
>

>=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" <Todd@discussions.microsoft.com> wrote in message
news:E18EDC60-DF60-4259-9F23-C07BCB33A728@microsoft.com...
> 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" <Todd@discussions.microsoft.com> wrote in message
>> news:2B43C961-C779-44BF-A4F2-EA8FACCBFF5B@microsoft.com...
>> > 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" <Todd@discussions.microsoft.com> wrote in message
>> >> news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
>> >> > 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,
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" wrote:

> >=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" <Todd@discussions.microsoft.com> wrote in message
> news:E18EDC60-DF60-4259-9F23-C07BCB33A728@microsoft.com...
> > 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" <Todd@discussions.microsoft.com> wrote in message
> >> news:2B43C961-C779-44BF-A4F2-EA8FACCBFF5B@microsoft.com...
> >> > 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" <Todd@discussions.microsoft.com> wrote in message
> >> >> news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Can you send me a copy of the file?

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

Biff

"Todd" <Todd@discussions.microsoft.com> wrote in message
news:B21E1181-3A72-435B-B61F-6DFD1E46DB14@microsoft.com...
> 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" wrote:
>
>> >=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" <Todd@discussions.microsoft.com> wrote in message
>> news:E18EDC60-DF60-4259-9F23-C07BCB33A728@microsoft.com...
>> > 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" <Todd@discussions.microsoft.com> wrote in message
>> >> news:2B43C961-C779-44BF-A4F2-EA8FACCBFF5B@microsoft.com...
>> >> > 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" <Todd@discussions.microsoft.com> wrote in message
>> >> >> news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
>> >> >> > 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

todd@aerotechhvac.com is my e-mail
Thanks


"Biff" wrote:

> Can you send me a copy of the file?
>
> If so, just let me know how to contact you.
>
> Biff
>
> "Todd" <Todd@discussions.microsoft.com> wrote in message
> news:B21E1181-3A72-435B-B61F-6DFD1E46DB14@microsoft.com...
> > 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" wrote:
> >
> >> >=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" <Todd@discussions.microsoft.com> wrote in message
> >> news:E18EDC60-DF60-4259-9F23-C07BCB33A728@microsoft.com...
> >> > 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" <Todd@discussions.microsoft.com> wrote in message
> >> >> news:2B43C961-C779-44BF-A4F2-EA8FACCBFF5B@microsoft.com...
> >> >> > 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" <Todd@discussions.microsoft.com> wrote in message
> >> >> >> news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
> >> >> >> > 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Ok, sent an email.

Biff

"Todd" <Todd@discussions.microsoft.com> wrote in message
news:5AA46F08-DB74-4ACA-BB43-869F103F2196@microsoft.com...
> todd@aerotechhvac.com is my e-mail
> Thanks
>
>
> "Biff" wrote:
>
>> Can you send me a copy of the file?
>>
>> If so, just let me know how to contact you.
>>
>> Biff
>>
>> "Todd" <Todd@discussions.microsoft.com> wrote in message
>> news:B21E1181-3A72-435B-B61F-6DFD1E46DB14@microsoft.com...
>> > 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" wrote:
>> >
>> >> >=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" <Todd@discussions.microsoft.com> wrote in message
>> >> news:E18EDC60-DF60-4259-9F23-C07BCB33A728@microsoft.com...
>> >> > 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" <Todd@discussions.microsoft.com> wrote in message
>> >> >> news:2B43C961-C779-44BF-A4F2-EA8FACCBFF5B@microsoft.com...
>> >> >> > 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" <Todd@discussions.microsoft.com> wrote in message
>> >> >> >> news:5DAE58CD-8982-4C6C-B5CB-97B55E838DA6@microsoft.com...
>> >> >> >> > 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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>