Trying to sum a range based on criteria in a table

B

BillyEL

New Member
Joined
Nov 24, 2017
Messages
2
I'm trying to sum hour many flight hours I have loaded in a spreadsheet. The insurance company wants various totals. The one I'm having trouble with the number of hours in a plane with retractable gear. How can I sum the flight time for all the planes that have retract time? I tried a SUMIFS, but I can't get that to work. Any ideas?

thanks

Here is what the table of planes looks like:
[TABLE="width: 310"]
<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;"> <col width="62" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2190;" span="2"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2133;"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2360;"> <col width="82" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2901;"> <tbody>[TR]
[TD="class: xl69, width: 82, bgcolor: transparent"]Model #[/TD]
[TD="class: xl69, width: 62, bgcolor: transparent"]Tail #[/TD]
[TD="class: xl70, width: 62, bgcolor: transparent"]Category[/TD]
[TD="class: xl72, width: 60, bgcolor: transparent"]Complex[/TD]
[TD="class: xl72, width: 66, bgcolor: transparent"]High Perf.[/TD]
[TD="class: xl78, width: 82, bgcolor: transparent"]Retract Gear[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]C182Q[/TD]
[TD="class: xl73, bgcolor: transparent"]N12345[/TD]
[TD="class: xl73, bgcolor: transparent"]ASEL[/TD]
[TD="class: xl73, bgcolor: transparent"]No[/TD]
[TD="class: xl73, bgcolor: transparent"]Yes[/TD]
[TD="class: xl75, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]PA46-350P[/TD]
[TD="class: xl65, bgcolor: transparent"]N11224[/TD]
[TD="class: xl65, bgcolor: transparent"]ASEL[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl75, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]PA46T-DLX[/TD]
[TD="class: xl65, bgcolor: transparent"]N11224[/TD]
[TD="class: xl65, bgcolor: transparent"]ASEL[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl75, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]PA32R-301T[/TD]
[TD="class: xl65, bgcolor: transparent"]N00001[/TD]
[TD="class: xl65, bgcolor: transparent"]ASEL[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl75, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]PA28A-181[/TD]
[TD="class: xl65, bgcolor: transparent"]N00002[/TD]
[TD="class: xl65, bgcolor: transparent"]ASEL[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[TD="class: xl75, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]PC-12/47[/TD]
[TD="class: xl65, bgcolor: transparent"]N55DD[/TD]
[TD="class: xl65, bgcolor: transparent"]ASEL[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl75, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]SIMPRO[/TD]
[TD="class: xl65, bgcolor: transparent"]SIMPRO1[/TD]
[TD="class: xl65, bgcolor: transparent"]ASEL[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl75, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]PC-12/47Sim[/TD]
[TD="class: xl65, bgcolor: transparent"]SIM500[/TD]
[TD="class: xl65, bgcolor: transparent"]ASEL[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[TD="class: xl75, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl77, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Here is what the Log looks like:
[TABLE="width: 154"]
<colgroup><col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2929;"> <col width="62" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2190;" span="2"> <tbody>[TR]
[TD="class: xl77, width: 144, bgcolor: transparent, colspan: 2"]Aircraft[/TD]
[TD="class: xl79, width: 62, bgcolor: transparent"]Flight Time[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Make & Model[/TD]
[TD="class: xl66, bgcolor: transparent"]Tail Number[/TD]
[TD="class: xl67, bgcolor: transparent"]H:M[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]PA28A-181[/TD]
[TD="class: xl69, bgcolor: transparent"]N36700[/TD]
[TD="class: xl70, bgcolor: transparent"]1.00[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]C182Q[/TD]
[TD="class: xl69, bgcolor: transparent"]N97523[/TD]
[TD="class: xl71, bgcolor: transparent"]1.10[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]PA46-350P[/TD]
[TD="class: xl69, bgcolor: transparent"]N744RV[/TD]
[TD="class: xl71, bgcolor: transparent"]1.10[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]PA46T-DLX[/TD]
[TD="class: xl69, bgcolor: transparent"]N744RV[/TD]
[TD="class: xl71, bgcolor: transparent"]0.90[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]PA32R-301T[/TD]
[TD="class: xl69, bgcolor: transparent"]N4187D[/TD]
[TD="class: xl71, bgcolor: transparent"]1.20[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]PC-12/47[/TD]
[TD="class: xl69, bgcolor: transparent"]N51DJ[/TD]
[TD="class: xl71, bgcolor: transparent"]1.20[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]PA46-350P[/TD]
[TD="class: xl69, bgcolor: transparent"]N744RV[/TD]
[TD="class: xl71, bgcolor: transparent"]2.00[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]PA46T-DLX[/TD]
[TD="class: xl69, bgcolor: transparent"]N744RV[/TD]
[TD="class: xl71, bgcolor: transparent"]1.00[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]PA28A-181[/TD]
[TD="class: xl69, bgcolor: transparent"]N36700[/TD]
[TD="class: xl71, bgcolor: transparent"]1.10[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]SIMPRO[/TD]
[TD="class: xl69, bgcolor: transparent"]SIMPRO1[/TD]
[TD="class: xl73, bgcolor: transparent"]2.10[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]pc-12/47Sim[/TD]
[TD="class: xl75, bgcolor: transparent"]FAA1373[/TD]
[TD="class: xl76, bgcolor: transparent"]1.40[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hi Billy,

If I have understood you correctly, you should just need to put this in the desired column in your table of planes:

=IF(F2="No","",SUMIF(Log!A:A,A2,Log!C:C))

Cheers
JB
 
Upvote 0
Is this what you are looking for,


Unknown
ABCDEF
1Model #Tail #CategoryComplexHigh Perf.Retract Gear
2C182QN12345ASELNoYesNo
3PA46-350PN11224ASELYesYesYes
4PA46T-DLXN11224ASELYesYesYes
5PA32R-301TN00001ASELYesYesYes
6PA28A-181N00002ASELNoNoNo
7PC-12/47N55DDASELYesYesYes
8SIMPROSIMPRO1ASELNoYesNo
9PC-12/47SimSIM500ASELYesYesYes
Sheet2



Unknown
HIJ
1AircraftFlight Time
2Make & ModelTail NumberH:M
3PA28A-181N367001
4C182QN975231.1
5PA46-350PN744RV1.1
6PA46T-DLXN744RV0.9
7PA32R-301TN4187D1.2
8PC-12/47N51DJ1.2
9PA46-350PN744RV2
10PA46T-DLXN744RV1
11PA28A-181N367001.1
12SIMPROSIMPRO12.1
13pc-12/47SimFAA13731.4
Sheet2



Unknown
MN
1Make & ModelFlight Time
2PA28A-181 
3C182Q
4PA46-350P3.1
5PA46T-DLX1.9
6PA32R-301T1.2
7PC-12/471.2
8SIMPRO
9pc-12/47Sim1.4
Sheet2
Cell Formulas
RangeFormula
N2=IF(VLOOKUP(M2,$A$2:$F$9,COLUMNS($A$2:$F$2),0)="Yes",SUMIF($H$3:$H$13,M2,$J$3:$J$13),"")
 
Last edited:
Upvote 0
I really just need a total for all planes that are retractable gear. I guess I could add a column for each plane and then add them up, but I was hoping I could come up with a formula that would do it all without a bunch of helper columns.
 
Upvote 0
I'm sure there will be a way - just not one that I am aware of. But there are some smart people on here, I think you could probably achieve this with some kind of crazy array formula, but if it were me, I would just put a few lines of code together and handle it through VBA.

If you're open to the VBA option let me know and I'll put something together.

Cheers
JB
 
Upvote 0
It would probably help if you provided data that actually matched? I can only find 1 match between those 2 tables (SIMPRO1). Also, if you update with more realistic data, please show what you expect - sand how you arrived at that?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
2K
MickG
M
C
  • Question Question
SUMIF - Based On Unique Values and Multiple Criteria
2 3
Replies
25
Views
11K
bronxjm
B
L
  • Question Question
How to filter data in a Pivot Table when data is Sum
Replies
5
Views
1K
DanteAmor
DanteAmor
D
  • Question Question
Using COUNTA horizontally
Replies
3
Views
3K
Joe4
Joe4
S
  • Question Question
Sum of categories/referenced to table
Replies
3
Views
570
RasGhul
RasGhul

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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