Search code examples
exceloffsetsumifs

Find criteria in multiple columns, and sum an offset of each matched value

I need to get a total sum of values from multiple columns based on a criteria a set amount of columns away from each value.

For example:

Column 1   Column2      Column3  Column4    Column5      Column6
Staff 1                          staff2
status --- location --- cost     status --- location --- cost
g          london       500      y          manchester   250
y          birmingham   450      g          scotland     100

I want to sum all the costs where the status is "g". I've been trying to sum, sumif, sumifs, sumproduct, offset and find, I'm just not sure of the correct combination of functions to do this.

Any help would be appreciated.

Thanks

Solution

  • Something like:

    =SUMPRODUCT((A:A="g") * (C:C))+SUMPRODUCT((D:D="g") * (F:F))