Need Help With AverageIF with specific text

[edisnotsmart]

I am trying to find the average of specific states (four to be exact, Alaska, Nevada, Florida and Texas) in my list of excel but I keep getting errors.

I've tried: =AVERAGEIF(A1:A10,"*Texas*","*Nevada*","*Florida*","*Alaska*",B1:B10) but its tell me I have too many arguments. Can anyone help me find a solution to average multiple specific states?

Column A
Cell 1: Texas
Cell 2: Florida
Cell 3: Colorado
Cell 4: Maine
Cell 5: Nevada
Cell 6: Alaska
Cell 7: Texas
Cell 8: Nevada
Cell 9: Florida
Cell 10: Utah

Column B
Cell 1: 138
Cell 2: 148
Cell 3: 982
Cell 4: 458
Cell 5: 256
Cell 6: 125
Cell 7: 236
Cell 8: 123
Cell 9: 258
Cell 10: 236

 

[mart37]

=SUMPRODUCT((A1:A10="Texas")+(A1:A10="Nevada")+... ;B1:B10)/SOMPRODUCT((A1:A10="Texas")+(A1:A10="Nevada")+...*1)

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Texas	138		183.429
2	Florida	148
3	Colorado	982
4	Maine	458
5	Nevada	256
6	Alaska	125
7	Texas	236
8	Nevada	123
9	Florida	258
10	Utah	236
Sheet

 

[edisnotsmart]

=SUMPRODUCT((A1:A10="Texas")+(A1:A10="Nevada")+... ;B1:B10)/SOMPRODUCT((A1:A10="Texas")+(A1:A10="Nevada")+...*1)

For some reason it doesn't allow me to get past =SUMPRODUCT((A1:A10="Texas")+(A1:A10="Nevada") when I start putting ;B1:B10 I get a notification saying that they found something wrong with the formula. Can't figure out what tho. Thank you for replying back!

 

[edisnotsmart]

This actually worked!! Thank you, really appreciate it. This was driving me crazy!

 

[edisnotsmart]

This actually worked!! Thank you, really appreciate it. This was driving me crazy!

---

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

	A	B	C	D
Texas
Florida
Colorado
Maine
Nevada
Alaska
Texas
Nevada
Florida
Utah

<colgroup><col style="width:30px; "><col style="width:102px;"><col style="width:64px;"><col style="width:39px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]138[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]183.429[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]148[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]982[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]458[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]256[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]125[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]236[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]123[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]258[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]236[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D1 {=AVERAGE(IF(ISNUMBER(SEARCH({"Texas","Nevada","Florida","Alaska"},A1:A10)),B1:B10))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

You're welcome. Thanks for the feedback.