Returning a Sum of Values from Spreadsheet A Based on the Value of a Cell in Spreadsheet B

Google Sheets
1

=ifna(query(Transactions!$B$1:$R, “select sum(E) where B >= date ‘2020-01-01’and B <= date ‘2020-01-31’ and D = ‘Paycheck’ Label sum(E)’’”, 1),iferror(1/0))

This actually works, but instead of D=‘Paycheck’ (from Spreadsheet A) - I would love to replace the verbiage ‘Paycheck’ with the D=value from cell A3 (of Spreadsheet B). The query resides in Spreadsheet B. That way, I could copy the formula for each row rather than having to change the text for each instance.

There may be a more direct way to accomplish this, but I am trying to return the sum dollars of the category ‘Paycheck’ for the month of January 2020.

Hopefully not too confusing…

2

Here’s how I would modify your formula to do what you want, I’ve put my changes in bold:

=ifna(query(Transactions!$B$1:$R, “select sum(E) where B >= date ‘2020-01-01’ and B <= date ‘2020-01-31’ and D = ‘“&A3&”’ Label sum(E)‘’”, 1),iferror(1/0))

Just to be extra clear, that’s a single quote, then a double quote, then the & symbol. The & symbol is what you’re looking for, it can combine strings. Let me know if it works!

EDIT:
I was thinking about how else to do this besides using the query function, and I think SUMIFS would also work. Here’s my version:

=sumifs(Transactions!E:E,arrayformula(text(Transactions!B:B,“YYYY-MM”)),“2020-01”,Transactions!D:D,A3)

I don’t know if it’s any faster, but I thinks it’s a little simpler. I also used ARRAYFORMULA and TEXT functions to reformat the date to just be month and year, so all it’s looking for is the matching year-month combo instead of doing a greater than/less than comparison. I hope that makes sense, let me know if you have any questions.

1 Like
3

I was able to get your first suggestion to work, but haven’t been able to make the sumifs with the arrayformula to pull in the data. I continue to get an error message there. Thank you very much for your time and assistance to move me forward here.

4

I just tried copying and pasting my formula into google sheets, and for some reason the quotation marks aren’t working right. You could try deleting and replacing the quotation marks and see if that helps. One clue is that the text in quotes should be green in the formula bar, if it isn’t then sheets isn’t interpreting it correctly.

Hopefully that fixes it! If not let me know.