1

I posted a question yesterday and pulling tons of data from a table using sumifs function; however, I've figured most of it out. I've switched the formula to a query. Right now, my formula looks like this:

=query(B:AK,"SELECT C,F,I,M,O,P,S,U,Y,AC,AJ,AK

WHERE C <> '"&AP8&"' 
and C <> '"&AP12&"'
and C <> '"&AP2&"'
and C <> '"&AP3&"'
and C <> '"&AP4&"'
and C <> '"&AP5&"'
and C <> '"&AP6&"'
and C <> '"&AP7&"'
and C <> '"&AP9&"'
and C <> '"&AP10&"'
and C <> '"&AP11&"'
and C <> '"&AP13&"'
and C <> '"&AP14&"'
and C <> '"&AP15&"'
and C <> '"&AP16&"'
and C <> '"&AP17&"'
and C <> '"&AP18&"'
and C <> '"&AP19&"'
and C <> '"&AP20&"'
and C <> '"&AP21&"'
and C <> '"&AP22&"'
and C <> '"&AP23&"'
and C <> '"&AP24&"'
and C <> '"&AP25&"'
and C <> '"&AP26&"'
and C <> '"&AP27&"'
and C <> '"&AP28&"'
and C <> '"&AP29&"'
and C <> '"&AP30&"'
and C <> '"&AP31&"'
and C <> '"&AP32&"'
and C <> '"&AP33&"'
and C <> '"&AP34&"'
and C <> '"&AP35&"'
and C <> '"&AP36&"'
and C <> '"&AP37&"'
and C <> '"&AP38&"'
and C <> '"&AP39&"'
and C <> '"&AP40&"'
and C <> '"&AP41&"'
and C <> '"&AP42&"'
and C <> '"&AP43&"'
and C <> '"&AP44&"'
and C <> '"&AP45&"'
and C <> '"&AP46&"'
and C <> '"&AP47&"'
and C <> '"&AP48&"'
and C <> '"&AP49&"'
and C <> '"&AP50&"'
and C <> '"&AP51&"'
and C <> '"&AP52&"'
and C <> '"&AP53&"'
and C <> '"&AP54&"'
and C <> '"&AP55&"'
and C <> '"&AP56&"'
and C <> '"&AP57&"'
and C <> '"&AP58&"'
and C <> '"&AP59&"'
and C <> '"&AP60&"'
and C <> '"&AP61&"'
and C <> '"&AP62&"'
and C <> '"&AP63&"'
and C <> '"&AP64&"'
and C <> '"&AP65&"'
and C <> '"&AP66&"'
and C <> '"&AP67&"'
and C <> '"&AP68&"'
and C <> '"&AP69&"'
and C <> '"&AP70&"'

and AC <> '"&AQ2&"'
and AC <> '"&AQ3&"'
and AC <> '"&AQ4&"'
and AC <> '"&AQ5&"'

and U <> '"&AR2&"'
and U <> '"&AR3&"'
and U <> '"&AR4&"'
and U <> '"&AR5&"'

and M <> '"&AS2&"'
and M <> '"&AS3&"'
and M <> '"&AS4&"'
and M <> '"&AS5&"'
and M <> '"&AS6&"'
and M <> '"&AS7&"'
and M <> '"&AS8&"'
and M <> '"&AS9&"'
and M <> '"&AS10&"'
and M <> '"&AS11&"'
"
)

This is going to let my dynamically edit my sheet instead of going into a formula and editing 50+ sumifs functions. However, I'm stuck here because now I have a column O:O that I need to be able to say.. select if the cell contains text from AO3 OR AO4 OR AO5 etc.etc. as well as does not contain all of the information I've already input.

What I have in my formula thus far basically excludes all the information that I don't want, but now I need to input information that I DO want.

Here's the spreadsheet I'm working on now: https://docs.google.com/spreadsheets/d/1ZYzLWIXeNR6R9ugAiTmRcXEshJBZDpN_xWCAumpJujY/edit?usp=sharing

Improve this question
1
  • The post lacks a clearly specified question. Consider showing your manually entered desired results in the sample spreadsheet. For completeness, post a reasonable amount of representative sample data as a table directly in the question, together with your desired results.
    – doubleunary
    Commented Sep 3, 2022 at 17:15

1 Answer 1

Reset to default
1

This formula will get the same results as your monster query() and should be easier to manage:

=filter( 
  { C2:C, F2:F, I2:I, M2:M, O2:O, P2:P, S2:S, U2:U, Y2:Y, AC2:AC, AJ2:AJ, AK2:AK }, 
  isna(match(C2:C, AP2:AP, 0)), 
  isna(match(M2:M, AS2:AS, 0)), 
  isna(match(U2:U, AR2:AR, 0)), 
  isna(match(AC2:AC, AQ2:AQ, 0)) 
)

To add a condition like "column O2:O contains a value that appears in column ZZ2:ZZ", use the same pattern, but without the isna(). To implement an "or" condition, use Boolean arithmetic.

Improve this answer
3
  • @doulbeunary This is really helpful, thank you! Only issue I'm having now is inputting the Booblean logic. So I've filtered for answers based on... =filter( { C2:C, F2:F, I2:I, M2:M, O2:O, P2:P, S2:S, U2:U, Y2:Y, AC2:AC, AJ2:AJ, AK2:AK }, isna(match(C2:C, AP2:AP, 0)), isna(match(M2:M, AS2:AS, 0)), isna(match(U2:U, AR2:AR, 0)), isna(match(AC2:AC, AQ2:AQ, 0)), match(M2:M,AN2:AN,0) (added this) However, I'm now trying to add another filter to it using (+); however, it's not working and it's giving me "FILTER range must be a single row or a single column."
    – travisg
    Commented Sep 4, 2022 at 17:30
  • 1
    Use this pattern =filter( C2:C, match(M2:M, BM2:BM, 0) + match(N2:N, BN2:BN, 0) ). Please ask only one question per post.
    – doubleunary
    Commented Sep 4, 2022 at 18:43
  • Amazing, thank you for your help @doubleunary!
    – travisg
    Commented Sep 4, 2022 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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