Can I combine AVERAGEIFS, SUMPRODUCT, and AND?

rgsanchez

New Member
Joined
Dec 1, 2016
Messages
11
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Train[/TD]
[TD]Passengers[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]702[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]752[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]757X[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]737X[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]738X[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]700[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Sat[/TD]
[TD]738X[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]List[/TD]
[/TR]
[TR]
[TD]702[/TD]
[/TR]
[TR]
[TD]752[/TD]
[/TR]
[TR]
[TD]757X[/TD]
[/TR]
[TR]
[TD]737X[/TD]
[/TR]
[TR]
[TD]738X[/TD]
[/TR]
</tbody>[/TABLE]

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 ?
 
Welcome to the forum.

How about:

Excel 2012
ABC
Total

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Day[/TD]
[TD="bgcolor: #FFFFFF"]Train[/TD]
[TD="bgcolor: #FFFFFF"]Passengers[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]702[/TD]
[TD="bgcolor: #FFFFFF"]5[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]752[/TD]
[TD="bgcolor: #FFFFFF"]10[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]757X[/TD]
[TD="bgcolor: #FFFFFF"]20[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]737X[/TD]
[TD="bgcolor: #FFFFFF"]2[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]738X[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]700[/TD]
[TD="bgcolor: #FFFFFF"]100[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]Sat[/TD]
[TD="bgcolor: #FFFFFF"]738X[/TD]
[TD="bgcolor: #FFFFFF"]100[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]List[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]702[/TD]
[TD="align: right"][/TD]
[TD="align: right"]38[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]752[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]757X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF"]737X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF"]738X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2:A8="Fri"),--ISNUMBER(MATCH(B2:B8,A13:A17,0)),C2:C8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Welcome to the forum.

How about:

Excel 2012
ABC
Total

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Day[/TD]
[TD="bgcolor: #FFFFFF"]Train[/TD]
[TD="bgcolor: #FFFFFF"]Passengers[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]702[/TD]
[TD="bgcolor: #FFFFFF"]5[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]752[/TD]
[TD="bgcolor: #FFFFFF"]10[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]757X[/TD]
[TD="bgcolor: #FFFFFF"]20[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]737X[/TD]
[TD="bgcolor: #FFFFFF"]2[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]738X[/TD]
[TD="bgcolor: #FFFFFF"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]Fri[/TD]
[TD="bgcolor: #FFFFFF"]700[/TD]
[TD="bgcolor: #FFFFFF"]100[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]Sat[/TD]
[TD="bgcolor: #FFFFFF"]738X[/TD]
[TD="bgcolor: #FFFFFF"]100[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]List[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]702[/TD]
[TD="align: right"][/TD]
[TD="align: right"]38[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]752[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]757X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF"]737X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF"]738X[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2:A8="Fri"),--ISNUMBER(MATCH(B2:B8,A13:A17,0)),C2:C8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

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