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

Question

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)

Answer 1

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.