CODEWITHSUNDEEP
google-sheets
egflame
egflame

Reputation: 167

Query for 2 criteria to return one value

I have a table for Mod name, Queue, No of cases, Avg. Handling Time, Productive Hours V1 and Actual Productive Hours.

https://docs.google.com/spreadsheets/d/19KJ6KVBWk2UH21LyPGlqJAml3Nx37ruXCrk9CQrXflE/edit#gid=0

For example: I want a formula to return the number of (Pending calls - EN for the Mod. Kim). I tried Vlook up and query and I don't really know why it's not working.

Excuse my noobness :(

Upvotes: 0

Views: 39

Answers (1)

JPV
JPV

Reputation: 27262

Try

=query({ArrayFormula(if(row(C9:C) <= max(if(not(isblank(D9:D)),row(C9:C))),vlookup(row(C9:C),filter({row(C9:C),C9:C},len(C9:C)),2),)), D9:H}, "Select Col1, count(Col2) where Col2 = 'Pending calls - EN' and Col1 = 'Kim' group by Col1 label count(Col2)''", 0)

or

=sumproduct(ArrayFormula(if(row(C9:C) <= max(if(not(isblank(D9:D)),row(C9:C))),vlookup(row(C9:C),filter({row(C9:C),C9:C},len(C9:C)),2),))="Kim", D9:D="Pending calls - EN")

and see if that works?

Also see the spreadsheet you shared, cells A1 and A2.

To get the count per 'mod' you can try

=query({ArrayFormula(if(row(C9:C) <= max(if(not(isblank(D9:D)),row(C9:C))),vlookup(row(C9:C),filter({row(C9:C),C9:C},len(C9:C)),2),)), D9:H}, "Select Col1, count(Col2) where Col2 = 'Pending calls - EN' group by Col1 label count(Col2)''", 0)

Upvotes: 2

Related Questions