{"id":36129,"date":"2016-06-23T12:23:14","date_gmt":"2016-06-23T06:53:14","guid":{"rendered":"https:\/\/2thenew.xyz\/blog\/?p=36129"},"modified":"2016-06-23T12:23:14","modified_gmt":"2016-06-23T06:53:14","slug":"how-to-use-arrayformula-sumif-sort-functions-and-referring-to-multiple-sheets-in-google-spreadsheet","status":"publish","type":"post","link":"https:\/\/2thenew.xyz\/blog\/how-to-use-arrayformula-sumif-sort-functions-and-referring-to-multiple-sheets-in-google-spreadsheet\/","title":{"rendered":"How to aggregate data by referring to multiple sheets in Google Spreadsheet using multiple functions"},"content":{"rendered":"

 <\/p>\n

Problem Statement<\/b><\/span>:<\/b><\/h1>\n

Suppose you are running a small business or creating a timesheet based cost calculations in any organization as a Lead, then you may need to refer data from different sheets within a single google spreadsheet to get the desired output\/result.<\/p>\n

Also, there might be a case where you want to see and manage the list of Top Buyers of your products or a list of Employees with highest cost first and that too you want it to be updated automatically as soon as you update the master sheet.<\/span><\/p>\n

This blog will help you in doing that with an ease.<\/span><\/p>\n

Example<\/b><\/span>:<\/span>
\nSmall Business where you are managing multiple sheets for different type of products with all the details of your products like its Selling Price, Cost Price, Buyers\u2019 Name etc. with something like this:<\/span><\/p>\n

Sheet 1: <\/span>Product Type 1<\/i><\/b>
\n\"GB_01\"<\/p>\n

Sheet 2: <\/span>Product Type 2<\/i><\/b>
\n\"GB_02\"<\/strong><\/strong><\/p>\n

Now, you want to create another sheet with Top Buyers on the basis of Selling Price of this sheet. For doing that you can follow the following steps:<\/span><\/p>\n

    \n
  1. First you have to find out a list of all the buyers of your products (there might be a case where a buyer has bought product on single type only)<\/span><\/li>\n
  2. Create a new sheet (say Buyers List) in same spreadsheet having these columns – Product Type 1, Product Type 2 <\/span>
    \n\"GB_03\"<\/li>\n
  3. To find the unique list of buyers for both Product Types, use this formula below Product Type 1:<\/span>
    \n
    =ArrayFormula(sort(unique(transpose(split(concatenate(‘Product Type 1′!J3:J<\/span>&”;”),”;”)))))<\/h6>\n

    \"GB_03_1\"
    \nwhere <\/span>\u2018Product Type 1′!J3:J<\/span><\/strong>\u00a0is the column having all the buyer’s name (Sold To)\u00a0<\/span>in sheet 1.<\/span><\/li>\n

  4. Use the same formula for Product Type 2 as well as for rest of the rows as well.<\/li>\n
  5. This will fetch and provide the list of unique customers\/buyers of all your Product Types and resulting this:
    \n\"GB_04\"<\/li>\n
  6. Create a new sheet (say Top Buyers Calculator) in same spreadsheet having these columns – Sold To, Product Type 1, Product Type 2 and Total. This is to find a list of all the buyers along with their total purchase of different Product Types.<\/li>\n
  7. To get a unique list of buyers of all your products for \u2018Sold To\u2019 column, use the same ArrayFormula referring \u2018Buyers List\u2019 sheet as shown below:
    \n\"GB_05\"
    \n<\/span>where, <\/span>\u2018Buyers List\u2019!B3:C<\/strong><\/span> => the range of all the buyers from Buyers List sheet.<\/li>\n
  8. Use the same function for all the columns to get the list of customers with their purchase values of different product types.<\/span><\/li>\n
  9. Now to find out the Top Buyers list, out of this calculator sheet, create another sheet with header columns as Sold To, Product Type 1, Product Type 2 and Total.<\/span><\/li>\n
  10. Use the SORT function to get a sorted list of all the values from Top Buyers Calculator sheet as shown below:
    \n\"GB_08\"<\/span><\/span>
    \nwhere,<\/span>
    \n\u2018Top Buyer Calculator\u2019!B3:E<\/span><\/strong> => Range of data to be sorted.<\/span>
    \n\u2018Top Buyer Calculator\u2019!E3:E <\/span><\/strong>=> Index of the column by which to sort (Total in our case)<\/span>
    \nFalse<\/span><\/strong> => Used to sort in descending order.<\/span>
    \n
    \nFollowing is the screenshot explaining the SORT function:
    \n\"GB_09\"<\/span><\/span><\/li>\n<\/ol>\n

    Output:<\/strong><\/span><\/h1>\n

    Using these steps and functions you will now have a list of<\/p>\n