Counting only specific values

[Mattias1]

Hello,
I am trying to construct a formula without using any macros and hoping someone can help me out. I have attached a sample workbook with the problem.
I would like a formula on a separate sheet ('Formula') which will consider the following.

If Aircraft!E:E is "Turbo-Propeller" Then use the Aircraft!A:A values to find all matching registrations, in this case C-XXXX, C-YYYY and C-ZZZZ are all Turbo-Propeller.
Using the determined registrations, Lookup if Logbook!C:C matches any of those registrations, to sum the associated row Logbook!Q:Q.

There will be multiple entries for each registration (i.e.: C-XXXX) and I would like all registrations to add up for a total value.
The end result will be a list of all aircraft which are considered "Turbo-Propeller" and will have the values added for each column separately.

Thanks!

Problem.xlsx

[Richard Buttrey]

Hi,

Your restriction that the solution can't include macros rather seems to rule out a simple solution.

Essentially the task boils down to filtering your Aircraft tab by Type, copying the filtered Registrations to another area and using that list of Regn's as the criteria range in a Data Advanced filter action. Once that filter has been imposed on your Logbook database (which will need a small layout change to ensure each column has a label header) you could then use an =SUBTOTAL(9,Q4:Q23) to return the total hours. All this you can of course do manually, just not with a standard Excel Function.

This is one of those cases where a fairly simple macro will achieve your result much more easily.

[Mattias1]

Thanks for the reply.

Is there no way to do this with formulas? I am keeping the file macro free so I can open it anywhere, on my phone, any computer, remote desktop etc. I would like to have all the functions operating so that I can extract information if required on the spot.
As for the layout change, I am unable to make any modifications as it matches paper copies.

I currently have a work-around where I use "=SUMIF(Logbook!B:B, "SW5", Logbook!M:M)" where SW5 is the only aircraft so far which will fall under the turbo-prop. I am looking for something where it applies to all turbo-props without me having to manually add another type to the equation each time.

Is it possible to filter the registration types via formula? I could then use a string of +SUMIF+SUMIF+... to add up the hours. It would be ideal if I could incorporate it into 1 formula so I do not have to have an undesired list visible.

Cheers.

[Richard Buttrey]

Hi,

If you carried the A/C type on the Logbook then of course you could use the SUMIF function.

The only way I can think of is with the attached. You'll need to add a new column to your A/C tab and the SUBTOTAL() function

[Mattias1]

Ok. I guess I cannot do what I am hoping to achieve.
I would have to create 10 new columns on that page for each scenario, day/night, single/multi, etc. And then use a SUMIF to take only the turbo-props.

I think it would be simpler to manually create a table of only aircraft types which are turbo-props and SUMIF if they appear on the list. I was hoping for a more automatic method since I will extend it to other types as well in the future.

Thanks for your help though, I appreciate it!