Query not returning all values, also adding extra if conditions
I'm having an issue with the spreadsheet linked below. I've chucked some dummy data in 'testing summary' sheet, and on the project and quarter sheet I've got a query set up so I can pull out the results per project for each quarter. It keeps weirdly missing the last column I want it to pull out (P and AD are the only ones with data so far) and I can't work out why. I'm using:
=iferror(if(I4 = "Quarter 1", query('Testing summary'!A6:BC,"select B,E,F,G,H,I,J,K,L,M,N,P where E = '"&$J$4&"'"),if(I4="Quarter 2",query('Testing summary'!A6:BC,"select B,R,S,T,U,V,W,X,Y,Z,AB,AC,AD where R = '"&$J$4&"'"),if(I4="Quarter 3",query('Testing summary'!A6:BC,"select B,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP where AE = '"&$J$4&"'"),IF(I4="Quarter 4",query('Testing summary'!A6:BC,"select B,AR,AS,AT,AU,AV,AW,AX,AY,AZ,BA,BB,BC WHERE AR = '"&$J$4&"'"))))),"No results found")
I think the quarter conditions have to be at the start because of the way the sheet's set up, they tell the query where to actually import from.
I'd also like to be able to pull out data by quarter, project and region, and can make that work with an AND at the end of each query (in the 3rd sheet, just added for Q1 so far), but if so I really need an 'all' version to work for regions, any idea? I am thinking I can add an extra set of if conditions somewhere to say where K4 = all then ignore the other options and pull everything that matches the other 2 conditions, but I have no idea where to start, to be honest.
I feel like I probably should have done something with filter rather than query, but I've never used filter!
Thanks for any help :)
looks like you just forgot O and AA column... try:
=IFERROR(
IF(I4="Quarter 1", QUERY('Testing summary'!A6:BC,"select B,E,F,G,H,I,J,K,L,M,N,O,P where E = '"&$J$4&"'"),
IF(I4="Quarter 2", QUERY('Testing summary'!A6:BC,"select B,R,S,T,U,V,W,X,Y,Z,AA,AB,AC where R = '"&$J$4&"'"),
IF(I4="Quarter 3", QUERY('Testing summary'!A6:BC,"select B,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP where AE = '"&$J$4&"'"),
IF(I4="Quarter 4", QUERY('Testing summary'!A6:BC,"select B,AR,AS,AT,AU,AV,AW,AX,AY,AZ,BA,BB,BC where AR = '"&$J$4&"'"))))),
"No results found")
also you can save 50+ characters by using:
=IFERROR(
IF(I4="Quarter 1", QUERY({'Testing summary'!B:B, 'Testing summary'!E:P}, "where Col2 = '"&J4&"'", ),
IF(I4="Quarter 2", QUERY({'Testing summary'!B:B, 'Testing summary'!R:AC}, "where Col2 = '"&J4&"'", ),
IF(I4="Quarter 3", QUERY({'Testing summary'!B:B, 'Testing summary'!AE:AP}, "where Col2 = '"&J4&"'", ),
IF(I4="Quarter 4", QUERY({'Testing summary'!B:B, 'Testing summary'!AR:BC}, "where Col2 = '"&J4&"'", ))))),
"No results found")
and save up to 170+ characters if you rename your sheet:
as for your 2nd question try:
=IFERROR(ARRAY_CONSTRAIN(
IF(I4="Quarter 1", QUERY({T!B:B, T!E:P, T!A:A}, "where Col2='"&J4&IF(K4="All",,"' and Col14='"&K4)&"'", ),
IF(I4="Quarter 2", QUERY({T!B:B, T!R:AD, T!A:A}, "where Col2='"&J4&IF(K4="All",,"' and Col14='"&K4)&"'", ),
IF(I4="Quarter 3", QUERY({T!B:B, T!AE:AP, T!A:A}, "where Col2='"&J4&IF(K4="All",,"' and Col14='"&K4)&"'", ),
IF(I4="Quarter 4", QUERY({T!B:B, T!AR:BC, T!A:A}, "where Col2='"&J4&IF(K4="All",,"' and Col14='"&K4)&"'", ))))),
9^9, 13), "No results found")
