Counting quantities in sales invoices based on title in google spreadsheet

Question:

( by Jared Jameson )

Hi, I'm currently working on an invoice and sales tracking system for book sales using google spreadsheets. The invoices are plugged in via google form and then a separate sheet in the workbook is used to sum the quantities sold on each invoice by title. The titles on the invoice sheet are in column F, and the titles on the sales tracker sheet are in column A. The quantities are spread across various columns  on the sheet depending on the format of the book sold (eg. hardcover, paperback, ebook, etc.) The sales tracker sheet has a column for each format, and in those columns I would like to put an array formula that could take the titles in column A on the sales tracker sheet and for every occurrence of that title on the invoices sheet sum the quantity sold in the relative column. Right now I am using a formula that is something like: =IFERROR(SUM(FILTER(Invoices!L2:L, Invoices!F2:F="Don Quixote")) 0). This formula works well enough to filter the titles and sum the quantities sold of each (in this example quantity sold would be in column L), but I have to manually enter the formula into each column when new titles are added. I want a formula that can be entered into the top cell of each column and then automatically update with each new title added. Any ideas on this? I would greatly appreciate any assistance. Thanks.

==================

What I would like is an array formula to go in the second cell of each column on the Yearly Sales Tally sheet that will sum all of the quantities on the Invoices sheet for each corresponding title on the Invoices sheet. In other words, there is one row for each title on the Yearly Sales Tally sheet that will be used to total quantities sold on the Invoices sheet, but on the Invoices sheet there may be multiple entries for the same title. The end result would be that the quantities for each format (hardcover, paperback, etc.) and each title be summed on the Invoices sheet. I would like to use an array so that the formula for each title won't have to be entered manually for each new title that is added to the list. I know this may still sound convoluted, but I hope it becomes clearer after you take a look at the sheet. As you will see, I originally thought I would use a simple SUM(FILTER() formula, but this requires writing each of the titles directly into the formula. Please let me know if there is anything else I can tell you to help come up with a solution, and I'd be happy to answer any questions you might have. Thanks!

==================

Let's try this again. Forgive me if my previous posts were confusing.

(1.) The "Invoices" sheet contains a great deal of data already, and these are the columns that we need to pay specific attention to. 

     

      (A.) Column F contains all the titles of books sold from each invoice that has been entered.

      (B.) Column L contains the number of each title that was sold in hardcover format (let's focus on hardcover format alone). This number is entered from the invoice along with the corresponding title.

(2.) Now, for each title, there may be multiple entries in column F from different invoices of all the customers who bought books. In other words, Three or four customers may have all bought the same title, which makes the title appear multiple times in column F. One customer might buy one copy while another might buy 5 copies etc. These quantities all show up in column L if the book they bought was in hardcover format and purchased at retail price. If they bought, say, a paperback version at a 20% discount then the number of copies they bought would be recorded in column AN. (As I suggested though let's just focus on hardcovers for now)

(3.) On the "Yearly Sales Tally" sheet, here are the columns that are relevant to my question.

       (A.) Column A contains a list of all the available titles for sale. Each title has it's own row and only appears once on the list.

       (B.) Column C needs to contain the total number of each title sold from the "Invoices" sheet. In other words, this column sums the numbers in Column L where the title is the same on the invoice (in column F) as the title in Column A of the "Yearly Sales Tally" sheet. 

What I want is an array formula that can be entered in cell C2 that would sum the numbers sold (column L) for each title. So, I'd like to be able to enter one formula that will match the title in cell A2 of the "Yearly Sales Tally" sheet with the multiple entries of that same title in column F of the "Invoices" sheet and then sum the numbers in column L of the "Invoices" sheet for those rows. Some of these cells in column L will be blank while some will contain a number. I want the formula to be an array so that it can perform this operation for all of the titles in column A. Currently, I have been using a sum filter formula but do not want to have to manually re-enter the sum filter formula every time a new title is added to the list in column A of the "Invoices" sheet. To clarify, Column C of the "Yearly Sales Tally" sheet needs to have an array formula in cell C2 that can sum the numbers from column L of the "Invoices" sheet for all rows where the title in column F of the "Invoices" sheet matches those in A2:A of the "Sales Tally" sheet...

Thanks for your patience, and I'm really hoping you'll be able to solve this. Please let me know if there is any additional information you need. I have also added some more data to the "Invoices" sheet for these titles "13 Ways of Happily", "1977", and "Map of Faring, A". These are the first three titles in column A of the "Yearly Sales Tally" sheet and the totals that should show up for them in column C of the "Yearly Sales Tally" sheet are 16. The same total for each title. Most of the other titles will total at 0 in column C of the "Yearly Sales Tally" sheet ( with the exception of one or two I think) since no invoices have been entered with quantities in column L of the "Invoices" sheet. Thanks again, and I hope this helps.

Solution:

Have a look at the following screenshots:

Sheet "Invoices":

Sheet "Yearly Sales Tally":

In the above sheet, I have the following formula in Cell C2:

=arrayformula(if(isblank(A2:A);iferror(1/0);mmult(sign(A2:A=TRANSPOSE('Invoices'!F2:F));'Invoices'!L2:L)))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive