Output counted list with extra qualifier

Question

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)

Answer 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.