1

I resell good online and track the profits on google sheets. I use a different sheet for each category of goods. I want to create a summary sheet that extracts the daily profits from all the sheets for all my sales for each date so that I can see how much I made every day w/o having to manually do it. I can figure out the formula for this.

All my dates on all the sheets are in column A and the profit column is K for all the sheets as well. Any help will be greatly appreciated, Thanks

1

2 Answers 2

3

Assuming you used 2 sheets (Tables! and Chairs!), the formula could be this:

=QUERY({Tables!A1:K;Chairs!A1:K}, "SELECT Col1, SUM(Col11) where Col1 is not null GROUP BY Col1",1)

put each different sheet between the 2 curly brackets, separated by semicolons.

Note that you can place the formula in any cell of another sheet or even in one of the sheets you already have, but only after column K.

3
  • Thank you so much
    – Spotbeam
    Aug 24, 2022 at 5:59
  • That formula has been working great but is there anyway to sort the dates in the column that your formula displays to arrange itself by newest date 1st instead of it starting with the oldest date at the top. I tried to click sort from Z-A and it switches it momentarily but then automatically switches it back the next second. Any help will be greatly appreciated, thanks
    – Spotbeam
    Aug 28, 2022 at 23:43
  • the sorting must be foreseen in the formula itself ... try this: =QUERY({Tables!A1:K;Chairs!A1:K}, "SELECT Col1, SUM(Col11) WHERE Col1 is not null GROUP BY Col1 ORDER BY Col1 desc",1)
    – Daniele
    Aug 29, 2022 at 6:26
1
=QUERY("ledger-tab-name:A1:K, "SELECT A, SUM(K) GROUP BY A", 1)

The link @Tedinoz shared could also work, but the OP asked for a way to automate the writing the dates. This should handle writing all dates and summing up the profits for you.

Query Language Documentation.

2
  • OP said: "I want to create a summary sheet that extracts the daily profits from ALL the sheets"
    – Daniele
    Aug 23, 2022 at 15:22
  • Ah yes, that part I didn't see. Your solution is definitely better. Thanks for the correction!
    – David Tan
    Aug 24, 2022 at 4:31

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.