# Can I combine AVERAGEIFS, SUMPRODUCT, and AND?

#### rgsanchez

##### New Member
 Day Train Passengers Fri 702 5 Fri 752 10 Fri 757X 20 Fri 737X 2 Fri 738X 1 Fri 700 100 Sat 738X 100

<tbody>
</tbody>

 List 702 752 757X 737X 738X

<tbody>
</tbody>

I want the sum of passengers that meet the following two criteria: 1)Rode the Train on a Friday, 2)Rode a train that was on the List. Also, I want to be able to select the List in a range, versus having to type in each train on the List individually.

Is there a way to do this by combining SUMIFS, AND, and SUMPRODUCT ?

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Eric W

##### MrExcel MVP
Welcome to the forum.

Excel 2012
ABC
1DayTrainPassengers
2Fri7025
3Fri75210
4Fri757X20
5Fri737X2
6Fri738X1
7Fri700100
8Sat738X100
9
10
11
12ListTotal
1370238
14752
15757X
16737X
17738X

</tbody>
Sheet1

Worksheet Formulas
CellFormula
C13=SUMPRODUCT(--(A2:A8="Fri"),--ISNUMBER(MATCH(B2:B8,A13:A17,0)),C2:C8)

</tbody>

<tbody>
</tbody>

#### rgsanchez

##### New Member
Welcome to the forum.

Excel 2012
ABC
1DayTrainPassengers
2Fri7025
3Fri75210
4Fri757X20
5Fri737X2
6Fri738X1
7Fri700100
8Sat738X100
9
10
11
12ListTotal
1370238
14752
15757X
16737X
17738X

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C13=SUMPRODUCT(--(A2:A8="Fri"),--ISNUMBER(MATCH(B2:B8,A13:A17,0)),C2:C8)

<tbody>
</tbody>

<tbody>
</tbody>

Wow, thank you,Eric! I had been at it for hours! Also, thank you for the welcome. Can you also think of a way to get the AVERAGE using the same two criteria?

#### Eric W

##### MrExcel MVP
For the average you can do this:

=SUMPRODUCT(--(A2:A8="Fri"),--ISNUMBER(MATCH(B2:B8,A13:A17,0)),C2:C8)/SUMPRODUCT(--(A2:A8="Fri"),--ISNUMBER(MATCH(B2:B8,A13:A17,0)))

#### rgsanchez

##### New Member
For the average you can do this:

=SUMPRODUCT(--(A2:A8="Fri"),--ISNUMBER(MATCH(B2:B8,A13:A17,0)),C2:C8)/SUMPRODUCT(--(A2:A8="Fri"),--ISNUMBER(MATCH(B2:B8,A13:A17,0)))

Eric, thank you so much! I was able to use your method for my analysis.

Replies
7
Views
548
Replies
0
Views
432
Replies
3
Views
1K
Replies
0
Views
843
Replies
4
Views
430

1,191,287
Messages
5,985,762
Members
439,981
Latest member
ofori francis

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back