Can I combine AVERAGEIFS, SUMPRODUCT, and AND?

rgsanchez

New Member
Joined
Dec 1, 2016
Messages
11
DayTrainPassengers
Fri7025
Fri75210
Fri757X20
Fri737X2
Fri738X1
Fri700100
Sat738X100

<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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Welcome to the forum.

How about:

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

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Welcome to the forum.

How about:

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?
 
Upvote 0
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)))

Glad to help!
:cool:
 
Upvote 0
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)))

Glad to help!
:cool:

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

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top