# Pulling data from Google Sheets tab and placing them into new tab but into the correct section for that month

I have a Google Sheet containing sales information; this data is inputted into a sheets tab where staff list common sale items: Client Name, Date, Month, and Invoice Amount.

My current Google Sheet (Link Removed) currently uses a SUMIF calculation to check the name of the client and then places the amount in a cell, but what I would like my formulae to do is to put the sales data into the sheet, but be placed into the correct column for that month.

In my Play Area version, I have three tabs, Play Area (in its current form, and then finished, how I would like it to act once the sales are auto-sorted into their corresponding months.

I assume I have to match the sale using a date format or by characters (month name); if I expand, the SUMIF has more than three arguments (null for SUMIF calculations) and causes an error.

The code I am using is:

``````=SUMIF('Sales 2023'!\$B\$5:B14,B8,'Sales 2023'!\$E\$5:E14)
``````
• Google Sheets is not Excel, so why do you have the Excel tag? Commented Mar 9, 2023 at 16:32
• Common mistake, apologies. Although a solution is very likely (almost probable) would work for both, however I now understand how this will incorrectly categorise content. @harrymc
– Owen O'Neill
Commented Mar 9, 2023 at 16:57
• you can add another condition by using `SUMIFS` instead of `SUMIF` Commented Mar 9, 2023 at 17:01

You can go about it in different ways.

Draggable Solution

``````=SUMIFS('Sales 2023'!\$E\$5:\$E\$14,'Sales 2023'!\$B\$5:\$B\$14,\$B9,'Sales 2023'!\$D\$5:\$D\$14,H\$5)
``````

`SUMIFS` is basicially `SUMIF` but with multiple criteria. The arguments are ordered differently though, so you'll have to move things around. The reference ranges must be set absolutely so that when we drag them, they do not move. Next, we need to set the criteria (Client # and Month) to be an absolute reference, but only for the row and column respectively.

Array Solution

``````=MAKEARRAY(
10,12,
LAMBDA(
row,col,
IFERROR(SUM(
FILTER('Sales 2023'!\$E\$5:\$E\$14,
'Sales 2023'!B\$5:\$B\$14=INDEX(\$B\$8:\$B\$17,row,1),
'Sales 2023'!D5:D14=INDEX(\$D\$5:\$O\$5,1,col)
)
))
)
)
``````

This solution makes use of the new `MAKEARRAY` function. It generates the entire table in a sort of roundabout way. We first declare the dimensions of the array, 10 rows by 12 columns (10 clients and 12 months). This does mean that you have to adjust this the number of clients to generate the table. Next, we pass in a `LAMBDA` function with two arguments, the row and column index. For each cell created by the array, we filter E5:E14 which contains the Invoice Amount by whether that row is associated with the correct month and client number. We do this by using the `FILTER` function in conjunction with the `INDEX` function. The `INDEX` function takes in an array and you give it 'coordinates' in order to return the value in that cell. We wrap the entire thing in `SUM` to ensure the values gets added up and `IFERROR` to clean up the error returned by an empty `FILTER`.