1

I currently have a table with the following columns:

Comm ID Item Name Months Orichalcum Tempest Jade Green Iron Weltsilver Ambergelt Beggar's Lye Dragonbone Iridescent Gloaming Total Materials Commissioned By CSID Ordered On Ordered For Materials Provided Money Provided (c) Artisan ID

To get a list that shows how many entries with a matching Item Name there are in an ordered list I use the following:

=ArrayFormula(QUERY(Commissions!B1:B&{"",""},"select Col1, count(Col2) where Col1 != '' group by Col1 order by count(Col2) label count(Col2)'Count'",1))

This works great and outputs a list like:

Item Name           Count
Biting Blade        1
Bloodcloak          1
Bullroarer's Shout  1
Butcher's Cleaver   1
Captain's Mask      1

I now want the same list but I want to only count entries that also have particular text inside the Ordered On col. I've tried working this out, but can't seem to find the correct syntax. (I also want to take the text it is looking for from a particular cell on a sheet rather than being hard coded at some stage)

1

The query you need is:

=QUERY(Commissions!B1:O,"select B,  count(C) where (B != '' and O = '"&B3&"') group by B order by count(C) label count(C)'Count'",1)

Please notice how we reffer to the cell '"&B3&"' so as to include it in our query.

I prepared for you a working sheet using cell B3. To the cell is added a drop-down validation for easier use.

  • Lovely, thank you! I thought I had tried that notation, but I must have had an error in my syntax. – solexious Sep 19 at 22:04
  • There are a couple of issues with your other queries as well. Have a look at my sheet. – marikamitsos Sep 19 at 22:05

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.