Formula Needed

[baggarwal]

Hi All:

I have data as follows:

Column A Column B Results
DLP English 10
DLP French 5
DLP French 5
DLP English 6

I need a formula that will average the numbers for a match in column A = DLP and match in Column B = English. Can someone please tell me which formula could do this.

 

[MrKowz]

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ColA</td><td style=";">ColB</td><td style=";">Results</td><td style="text-align: right;;"></td><td style=";">Excel 2003 or Older:</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">DLP</td><td style=";">English</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">Excel 2007 or Newer:</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">DLP</td><td style=";">French</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">DLP</td><td style=";">French</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">DLP</td><td style=";">English</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A2:A5="DLP"</font>),--(<font color="Red">B2:B5="English"</font>),C2:C5</font>)/SUMPRODUCT(<font color="Blue">--(<font color="Red">A2:A5="DLP"</font>),--(<font color="Red">B2:B5="English"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=AVERAGEIFS(<font color="Blue">C:C,A:A,"DLP",B:B,"English"</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Trevor G]

What about using AverageIFs as a formula if you are using Excel 2007 or
above.

Excel Workbook
	A	B	C	D	E	F	G
1	Types	Langauge	Result			Results	Average
2	DLP	English	10			English	8
3	DLP	French	5			French	5
4	DLP	French	5
5	DLP	English	6
Sheet1

 

[baggarwal]

Hi There, can you please tell me how you can also do a count of all the DLP and English combinations? I need a formula to do this.

 

[MrKowz]

Hi There, the average if formula does not work though if there is no value in column A. Can you tell me a formula that can do it even with blanks.

Can you please provide a larger sample of your data, as well as your desired output? If there are no values in column A, then your first condition that "Column A = DLP" isn't being held true.