Sum amount on a range if the respective row met the criteria

I'm trying to create a spreadsheet to calculate the sum of the hours on each months based on the respective rows. To make the example a little more clear, for excel below, I should get the value of 28 for Client 1, and 16 for Client 3.

I am trying to use the SUMIF function, but the sum range can't be on a multiple cells.

And this is how I'd like to see the information:

You can create a simple formula:

=SUM(IF(B5:B21="Client 1",G5:AK21))

That is doing an array calculation with 2 steps:

1. Return ENTIRE ROWS of numbers (even blanks) if column B = "Client 1".
2. Once I have all the rows (of which most will contain blanks), you simply sum those values.

You can replace "Client 1" in the formula with a reference to an external cell if you want. Also, make sure the entire range of the month is accurate in the second part of the formula G5:AK21.

Building on the excellent =SUM(IF()) approach from @Dave it is possible to use the OFFSET function to select the columns corresponding to each of the 12 months of the year.

The OFFSET function defines a rectangular range through its height and width, with the upper left corner of the range defined in terms of the number of rows below and columns to the right of a specified worksheet cell. In the approach below, I use the cell highlighted in yellow as the specified cell (cell E17).

Range A2:E14 (see screenshot, below) provides some information, or metadata, about where the columns of daily values are located for each of the 12 months of the year, relative to the highlighted cell. The useful values are in the columns headed Col_Offset and Columns so, for example, for January the daily values start at the 1st column to the right of the yellow cell and occupy 31 columns, for February the daily values start in the 32nd column to the right of the yellow cell and occupy 29 columns (changing the year to a non-leap year in cell B1 would change the value in cell E4 to 28), etc. The columns headed Month# and Cumulative are intermediate values required to calculate Col_Offset and Columns.

The formula for summarising the data totals by month and client can be written in a breakdown format as

=SUM(
IF(Sheet1!$B$18:$B$23=$A2,
OFFSET(Sheet1!$E$17,
1,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),
6,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4)
)
)
)

or, as a complete formula

=SUM(IF(Sheet1!$B$18:$B$23=$A2,OFFSET(Sheet1!$E$17,1,VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),6,VLOOKUP(B$1,Sheet1!$A$3:$E$14,5))))

where the metadata and daily data are assumed to be contained on worksheet Sheet1.

This formula applies to January/Client 1 and can be copied to the remaining cells for other months and clients, as partially shown in the screenshot below. Be sure that the month names used in the metadata and results table match exactly to avoid #N/A errors in the results.

The second and fourth arguments of the OFFSET function are 1 and 6 because the data starts in the first row after the yellow highlighted cell and, in the example shown, there are 6 rows of data. If there are a different number of rows simply substitute the correct value.

The third and fifth arguments simply use function VLOOKUP to look up the Col_Offset and Columns values in the metadata of the month involved.