I am working on sales bonuses in a sales tracking sheet. It is setup as
follows:

Column G H L O
21 DP $5,000 S
23 AF $10,000 S
I want to average column L if G=21, H=DP, and O=S. I entered:
Average(IF((G2:G100=U2),--(H2:H100=V3),L2:L100) (CTRL, SHIFT, ENTER).

The U2 and V3 are refrencing cells where I have put 21,23 and DP or AF as to
not make them equal "DP" or 21. I have also used the "DP" or 21 in the
equation. I cannot figure this out. Please help me.
Thanks

Todd,

You should be able to structure it using concatenation:

=average(if(g2:g100&h2:h100&l2:l100=u2&v3&"S",aver age_rng,""))

Ensure you press shift-control-enter

Replace average_rng with the range that will be averaged.
--
http://HelpExcel.com




"Todd" wrote:

I am working on sales bonuses in a sales tracking sheet. It is setup as
follows:

Column G H L O
21 DP $5,000 S
23 AF $10,000 S
I want to average column L if G=21, H=DP, and O=S. I entered:
Average(IF((G2:G100=U2),--(H2:H100=V3),L2:L100) (CTRL, SHIFT, ENTER).

The U2 and V3 are refrencing cells where I have put 21,23 and DP or AF as to
not make them equal "DP" or 21. I have also used the "DP" or 21 in the
equation. I cannot figure this out. Please help me.
Thanks

=AVERAGE(IF((G2:G100=U2)*(H2:H100=V3),L2:L100))
ctrl+shift+enter, not just enter

"Todd" wrote:

I am working on sales bonuses in a sales tracking sheet. It is setup as
follows:

Column G H L O
21 DP $5,000 S
23 AF $10,000 S
I want to average column L if G=21, H=DP, and O=S. I entered:
Average(IF((G2:G100=U2),--(H2:H100=V3),L2:L100) (CTRL, SHIFT, ENTER).

The U2 and V3 are refrencing cells where I have put 21,23 and DP or AF as to
not make them equal "DP" or 21. I have also used the "DP" or 21 in the
equation. I cannot figure this out. Please help me.
Thanks

=AVERAGE(IF((G2:G100=U2)*(H2:H100=V3)*(O2:O100="S" ),L2:L100,""))
Still entered with CTRL+SHIFT+ENTER.


--
Regards,
Dave


"Todd" wrote:

I am working on sales bonuses in a sales tracking sheet. It is setup as
follows:

Column G H L O
21 DP $5,000 S
23 AF $10,000 S
I want to average column L if G=21, H=DP, and O=S. I entered:
Average(IF((G2:G100=U2),--(H2:H100=V3),L2:L100) (CTRL, SHIFT, ENTER).

The U2 and V3 are refrencing cells where I have put 21,23 and DP or AF as to
not make them equal "DP" or 21. I have also used the "DP" or 21 in the
equation. I cannot figure this out. Please help me.
Thanks

Hi Todd

Try the non-array entered Sumproduct formula as follows

=SUMPRODUCT((G2:G100=U2)*(H2:H100=V3)*L2:L100)/
SUMPRODUCT((G2:G100=U2)*(H2:H100=V3))

--
Regards

Roger Govier


"Todd" wrote in message
...
I am working on sales bonuses in a sales tracking sheet. It is setup
as
follows:

Column G H L O
21 DP $5,000 S
23 AF $10,000 S
I want to average column L if G=21, H=DP, and O=S. I entered:
Average(IF((G2:G100=U2),--(H2:H100=V3),L2:L100) (CTRL, SHIFT, ENTER).

The U2 and V3 are refrencing cells where I have put 21,23 and DP or AF
as to
not make them equal "DP" or 21. I have also used the "DP" or 21 in
the
equation. I cannot figure this out. Please help me.
Thanks

I did not know you could use a concentration. Worked great and I will give
you a reply. Thank you very much.
Todd

"galimi" wrote:

Todd,

You should be able to structure it using concatenation:

=average(if(g2:g100&h2:h100&l2:l100=u2&v3&"S",aver age_rng,""))

Ensure you press shift-control-enter

Replace average_rng with the range that will be averaged.
--
http://HelpExcel.com




"Todd" wrote:

I am working on sales bonuses in a sales tracking sheet. It is setup as
follows:

Column G H L O
21 DP $5,000 S
23 AF $10,000 S
I want to average column L if G=21, H=DP, and O=S. I entered:
Average(IF((G2:G100=U2),--(H2:H100=V3),L2:L100) (CTRL, SHIFT, ENTER).

The U2 and V3 are refrencing cells where I have put 21,23 and DP or AF as to
not make them equal "DP" or 21. I have also used the "DP" or 21 in the
equation. I cannot figure this out. Please help me.
Thanks