1

So I work in marketing for a company and am analyzing survey data from our post purchase survey. I'm trying to sumifs with multiple criteria AND ranges as these ranges will update over time.

I've been able to come up with a formula that calculates this; however, it's requiring a TON of sumifs + sumifs functions, and I'd like to resolve that so it's simple and I can update easily.

Here's what I've got thus far....

=sumifs('Survey Responses'!D:D,'Survey Responses'!A:A,"<>AJ15",'Survey Responses'!A:A,"<>JON",'Survey Responses'!AA:AA,"<>GRIN",'Survey Responses'!S:S,"<>Paid_video",'Survey Responses'!K:K,"AJ Greene",'Survey Responses'!AI:AI,B3)

+sumifs('Survey Responses'!D:D,'Survey Responses'!A:A,"<>AJ15",'Survey Responses'!A:A,"<>JON",'Survey Responses'!AA:AA,"<>GRIN",'Survey Responses'!S:S,"<>Paid_video",'Survey Responses'!K:K,"Jon Seaton",'Survey Responses'!AI:AI,B3)

+sumifs('Survey Responses'!D:D,'Survey Responses'!A:A,"<>AJ15",'Survey Responses'!A:A,"<>JON",'Survey Responses'!AA:AA,"<>GRIN",'Survey Responses'!S:S,"<>Paid_video",'Survey Responses'!K:K,"iKnowAyrel",'Survey Responses'!AI:AI,B3)

+sumifs('Survey Responses'!D:D,'Survey Responses'!A:A,"<>AJ15",'Survey Responses'!A:A,"<>JON",'Survey Responses'!AA:AA,"<>GRIN",'Survey Responses'!S:S,"<>Paid_video",'Survey Responses'!N:N,"AJ",'Survey Responses'!AI:AI,B3)

Etc. etc.

I've created additional tables for the following:

  • Discount Codes to avoid
  • Survey Responses within column K =
  • Survey Responses within column N contain
  • Survey Responses AA:AA Do NOT Contain
  • Survey Responses S:S Do NOT Contain

Each of these columns will continuously update over time, which would make updating my previous formula hell.

To top it off, I will be measuring the sum of revenue within a particular month.

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

I feel like there has to be a way to do this with some array formulas, but it's hurting my head...

3
  • 1
    Does this answer your question? Google Sheets Query Function - Nested OR function among ands Sep 3 at 18:33
  • Your question lacks focus and detail. You've asked about an alternative to the existing sumifs formula, particularly as the number of variables may increase, and you have created lists of the variables. However, the existing sumifs don't reference all the variable values - so it isn't clear how you would expect to use the list values in your sumifs. I CAN visualise how you might select formula variables by dropdown cells. Also, FYI, a single query CAN substitute for the existing 13xsumifs. Would you please explain EXACTLY how you expect that you use the lists in your formula.
    – Tedinoz
    Sep 14 at 4:11
  • @doubleunary FWIW, I don't think this is a duplicate because I don't think this is a question about QUERY per se. True, a single query can do the work of the sumifs AND cope with the OP's lists of variables. But the existing formula don't reference all the list values. IMO, the question lacks detail about what the OP actually wants to do with these lists; my guess is that the OP may want to create dynamic formulas by using dropdowns referencing the lists but that's just a guess. I have asked the OP for an explanation. I would prefer to close pending those further details.
    – Tedinoz
    Sep 14 at 4:26

0

Your Answer

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

Browse other questions tagged or ask your own question.