Google Sheets - Query Function - Search Using Named Range

Question

I'm making a sheet where I fill in my expenses for each month. I have a tab setup with several columns listed as 'Groceries' or 'Travel' (for example) that are my Named Ranges:

Groceries	Travel
Tesco	Train
Asda	Parking

I would like the formula to look down my list of initial data which would look like so:

Expense	Cost
Asda	£1
Gifts	£2
Tesco	£2
Parking	£2

Then in each column it would show the relevant data, using the Named Ranges as the search key.

It would result in this:

Groceries	Cost	Travel	Cost
Asda	£1	Parking	£2
Tesco	£2

So I would like my query to search for anything mentioned in the 'Groceries' Named Range and show the result.

For example the query I'm thinking of would look something like this:

Groceries	Cost
=query($A$1:$B$5,"Select A where A = 'Groceries'),1)	=query($A$1:$B$5,"Select B where A = 'Groceries'),1)

Is this possible? And is it possible using only formulas? I've never used scripts before.

I'm currently using a typical query formula =query($A$1:$C$5,"Select A where C = 'Groceries'",1) and manually giving each expense a category in a column C.

Basically I'm hoping to make my sheet more automated to save me manually typing in what category each expense is, and I wondered if this was possible without using an ungodly amount of IFS as that's the only other way I know.

Blindspots · Accepted Answer · 2023-12-07 06:52:15Z

0

List by Groceries

It's simpler to use a FILTER function.

=FILTER(A:B, COUNTIF(Groceries, A:A))

Or, for Travel

=FILTER(A:B, COUNTIF(Travel, A:A))

SUM Groceries by Category

You could combine the FILTER and QUERY functions to group by grocery expense category.

FILTER in a QUERY

=QUERY(FILTER(A:B, COUNTIF(Groceries, A:A)),
   "Select Col1, SUM(Col2) group by Col1 
   label Col1 'Groceries', SUM(Col2) 'Total by Category'",0)

QUERY in a FILTER

LET is needed here to reduce the amount of code duplication.

=LET(groc,QUERY(A:B, "Select A, SUM(B) Where 
   A is not Null Group by A Label A 'Groceries',
   SUM(B) 'Total by Category'",1),
  FILTER(groc, COUNTIF(Groceries, INDEX(groc,,1))))

Function Support Links

COUNTIF FILTER LET QUERY SUM

answered Dec 7, 2023 at 6:52

Blindspots

1,0091 gold badge9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EchoB Over a year ago

That's fantastic and works brilliantly, exactly what I was looking for! I'm still learning with formulas so I appreciate such a clear explanation! Thank you for this!

Collectives™ on Stack Overflow

Google Sheets - Query Function - Search Using Named Range

1 Answer 1

List by Groceries

SUM Groceries by Category

FILTER in a QUERY

QUERY in a FILTER

Function Support Links

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

List by Groceries

SUM Groceries by Category

FILTER in a QUERY

QUERY in a FILTER

Function Support Links

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related