Using Query function to filter and sum sales values using up to 4 different criteria:

  • Agent
  • Month Sold
  • Date Sold
  • Product/MFG

This is in a "Dashboard" where each criteria can be selected from a Data validated drop-down list. It's all working great.

Except, what I can't figure out (and is probably something simple), is how to return "All Values" for each criteria (individually or in all combinations).

I've tried to think about every way my simpleton brain knows how. I would also like the flexibility to add criteria in the future. I initially sorted this out using a bunch of nested "if" "or" statements in a formula, but much prefer the query functionality.

Is there a way to setup a query that performs this functionality?

This is what I have and works great for a single variable. I know I can string these together for each variable using "and"...but I need to also be able to select all for each variable if the referenced value is "All 'variable'"

=query( {Applications!1:999}, "Select sum(" & F_Written & ") WHERE " & F_Agent & "= '"&B2&"'", -1)

I need it to sum the named range where the variable named range (in this case the "Agent" equals "Bob" if "Bob" is selected from the agent drop-down list, OR select all rows if "All Agents" is selected.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.