Formula needed, If sum

[ExcelNovice]

I'm working with a fairly large spreadsheet of 185 columns and about 2000 rows.
In the cells in row 5 are codes (ex: 4010, 4020, 4030 etc). Each column starting at row 8 has numerical data.
I'm looking for a formula that allows me to sum the values in a column based on the code in each in row 5.
Example: If 4020 is found in a cell in row 5, sum the values in that column starting at row 8.

Thanks for your help.

 

[maabadi]

Please Upload Example file & Desired Results with XL2BB ADDIN (Preferable) or upload at sites www.Dropbox.com , GoogleDrive OR OneDrive and Insert Link here.

 

[ExcelNovice]

Please Upload Example file & Desired Results with XL2BB ADDIN (Preferable) or upload at sites www.Dropbox.com , GoogleDrive OR OneDrive and Insert Link here.

Maabadi, see snippet below. Looking for formula that would allow me to sum the values in say column C based on the 9022 code in C5.
The formula should be something like this: find a specific code in row 5, then sum the value in the column where that code is found. Thanks.

 

[Fluff]

How about

=SUM(INDEX(A6:E1000,,MATCH(L5,A5:E5,0)))

where L5 has the 9022 or whatever column you want to sum

 

[ExcelNovice]

Awesome, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ExcelNovice]

Fluff, can the formula be extended to include additional conditions -
I want to extend the formula as follows - sum the correct column based on the value in cell in row 5 (as outlined in previous post), but only if a specific category is in col A and a specific type in col B.
That is for example, if D5 matches 9021 and all cells in column A is equaled to Cat3 and all cells in Column B is equaled to P7 then sum D6:D11.

Hope this is possible, thanks for your help.

 

[Jerrod Conomy]

I'm working with a fairly large spreadsheet of 185 columns and about 2000 rows.
In the cells in row 5 are codes (ex: 4010, 4020, 4030 etc). Each column starting at row 8 has numerical data.
I'm looking for a formula that allows me to sum the values in a column based on the code in each in row 5.
Example: If 4020 is found in a cell in row 5, sum the values in that column starting at row 8.

Thanks for your help.

Regards,

Jerrod Conomy

 

[Fluff]

How about

=SUMPRODUCT((C5:G5=K5)*(A6:A100=K6)*(B6:B100=K7),(C6:G100))

 

[ExcelNovice]

It works, thanks again!