Google Sheets embedded IF() in FILTER()

Question

I'm using data from GA and grouping it different ways to create a dashboard in Sheets. The data is primarily landing page, device type, sessions.

I split up sessions for each landing page based on device type, with each device type having its own sheet: desktop, tablet, and mobile. I also created a summary sheet, which is--you guessed it--the same data but not split into device type.

All said and done, the dashboard is pretty mediocre due to the small amount of data. I believe this can be at least presented better by using a data validation dropdown to allow people to select their device type on the summary sheet, causing the data to update for the device type selected. In this way, there's only one sheet. (I.e. Instead of switching between tabs, I choose "desktop" from the dropdown on the summary sheet and all the data updates to show only desktop numbers.)

I think this can be done with an embedded IF() statement in my filter. The trouble is, I'm not sure how to get the ELSE result to return what I need.

Expected result: Return sum of sessions matching landing page and device type if selected, or of all devices if "all" selected.

Actual result: Returning sum of sessions matching landing page and device type if selected (yay!), but returning 0 when "all" selected.

Current formula:

=SUM(
IFERROR(FILTER(
    'funnelflow-2steps'!C2:C,
    'funnelflow-2steps'!A2:A="/maple/",
IF(B13<>"all",
    'funnelflow-2steps'!B2:B=B1,""))
,0)
)

This calls the total sessions for landing page /maple/ matching the device type in cell B1
Where B1 = the cell with the data validation dropdown, with options for "all", "desktop", "tablet", and "mobile"

Sample data (this would be on the funnelflow-2steps sheet being referenced):

 landingPagePath | deviceCategory | sessions
-----------------|----------------|----------
 /chestnut/      | desktop        |        5
 /chestnut/      | tablet         |        2
 /chestnut/      | mobile         |        3
 /maple/         | desktop        |        1
 /maple/         | desktop        |        7
 /maple/         | mobile         |        6

EDIT: I turned the formula inside out, thinking having the IF() on the outside may solve my problem with the ELSE condition. This, unfortunately, ends in a parsing error.

=IF(B1="All",
SUM(IFERROR(FILTER(
    'funnelflow-2steps'!D2:D,
    'funnelflow-2steps'!A2:A="/maple/"),0))
SUM(IFERROR(FILTER(
    'funnelflow-2steps'!D2:D,
    'funnelflow-2steps'!A2:A="/maple/",
    'funnelflow-2steps'!B2:B=B1),0)))

Minadorae Minadorae · Accepted Answer · 2017-01-20T00:09:54

Nevermind, got it! I had to turn it inside out AND remember the comma between THEN and ELSE. Not like SQL FFS.

=IF(B1="All",
SUM(IFERROR(FILTER(
    'funnelflow-2steps'!D2:D,
    'funnelflow-2steps'!A2:A="/maple/"),0)),
SUM(IFERROR(FILTER(
    'funnelflow-2steps'!D2:D,
    'funnelflow-2steps'!A2:A="/maple/",
    'funnelflow-2steps'!B2:B=B1),0)))

Google Sheets embedded IF() in FILTER()

1 Answers