Can I combine AVERAGEIFS, SUMPRODUCT, and AND?

R

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 ?
 
Sort by date Sort by votes
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
Eric W said:
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]
Click to expand...

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
Eric W said:
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:
Click to expand...

Eric, thank you so much! I was able to use your method for my analysis.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
1K
arthurbr
arthurbr
O
  • Question Question
Combining SUMPRODUCT, AVERAGEIF and Wildcards in a single formula
Replies
7
Views
872
Peter_SSs
Peter_SSs
R
  • Question Question
Sumifs, Lists, and Sumproduct to create a matrix?
Replies
0
Views
688
rgsanchez
R
S
  • Solved
Sumifs - cells that did not match -- conditional format --Lookup, search, partial match, multiple criteria, lists, duplicate,
Replies
3
Views
2K
Fluff
Fluff
B
  • Question Question
COUNTIF / SUMPRODUCT with Multiple Criteria and/or Index Match
Replies
0
Views
1K
bansaladman
B

Share this page

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