Looking up and summing data in unconventional array

[GAD541898]

Hello All,

Seeking help looking up and summing data located in a single row using a function/formula that can be employed throughout the worksheet in Excel 365.

Specifically, my sheet is setup basically with Months across the top and various "creditors" leading the rows. Simplified version for this exercise has several columns per month, only two of importance: Method and Amount. I want to calculate and display in a cell at the end of applicable rows the total of Amount where Method is a specific text value, e.g. "FM Pro Rata" (which depending on the row, may occur once, or multiple times during the year span of the worksheet).

Method and Amount are adjacent columns each month. Essentially, how do I look up and sum up values adjacent to cells with specific text occurring in the same row?

[#2]

@GAD541898 

Try SUMIFS().

[#3]

@Detlef Lewin THANKS for suggesting using SUMIFS. I actually used SUMIF with the optional [sum_range]. The trick was to incorporate the OFFSET function to specify the sum_range. The formula created went like this:

SUMIF(F98:Z98,"FM Pro Rata",(OFFSET(F98:Z98,0,1)))

It looks at Row 98 searching for text FM Pro Rata, then using the OFFSET range on same row (0) and one column right (1) as the sum_range, sums the values from the adjacent cells as they occur along that single row from Col F to Col Z. 

[#4 · Accepted solution]

@GAD541898 

I'm pretty sure that OFFSET() is obsolete.

=SUMIF(F98:Z98,"FM Pro Rata",G98:AA98)

[#5]

Well THANKS once again! Who knew it would be that easy! Would have saved me a lot of time if there had been an example like my situation in the description of the SUMIF function in Help.