Trying to sum a range based on criteria in a table

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:
Model #Tail #CategoryComplexHigh Perf.Retract Gear
C182QN12345ASELNoYesNo
PA46-350PN11224ASELYesYesYes
PA46T-DLXN11224ASELYesYesYes
PA32R-301TN00001ASELYesYesYes
PA28A-181N00002ASELNoNoNo
PC-12/47N55DDASELYesYesYes
SIMPROSIMPRO1ASELNoYesNo
PC-12/47SimSIM500ASELYesYesYes
<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> </tbody>

Here is what the Log looks like:
AircraftFlight Time
Make & ModelTail NumberH:M
PA28A-181N367001.00
C182QN975231.10
PA46-350PN744RV1.10
PA46T-DLXN744RV0.90
PA32R-301TN4187D1.20
PC-12/47N51DJ1.20
PA46-350PN744RV2.00
PA46T-DLXN744RV1.00
PA28A-181N367001.10
SIMPROSIMPRO12.10
pc-12/47SimFAA13731.40
<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> </tbody>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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