Sumifs within Offset Match
 

Hi I need help!

I'm trying to create a sum formula using offset matches.

Essentially, I want avoid using sumifs since the columns could change order, but the headers will not change.

This data will feed into a template.
Example:
Category is column A, Sales is B, Cost is C and Units is C.
CATEGORY SALES COST UNITS
B
E
R
B
E
R

Category repeats throughout the data, but I might add additional metrics to the template and don't want to have to change the range of the sumif each time I modify the data or the report and would prefer to have the sum combined with a match formula.

Any help would be appreciated!
Let me know if clarification is needed.

Thanks!!

Sumifs within Offset Match
 

Hi,

Am Fri, 11 Sep 2015 09:48:40 +0100 schrieb ITexcelNY:

Category is column A, Sales is B, Cost is C and Units is C.
CATEGORY SALES COST UNITS
B
E
R
B
E
R

Category repeats throughout the data, but I might add additional metrics
to the template and don't want to have to change the range of the sumif
each time I modify the data or the report and would prefer to have the
sum combined with a match formula.

For example: If you want to sum COST for each "B" in Category:
=SUMIFS(INDEX(A:D,,MATCH("COST",A1:D1,0)),A:A,"B")


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Sumifs within Offset Match
 

Hi,

Am Fri, 11 Sep 2015 10:58:26 +0200 schrieb Claus Busch:

For example: If you want to sum COST for each "B" in Category:
=SUMIFS(INDEX(A:D,,MATCH("COST",A1:D1,0)),A:A,"B")

or use a Pivot table.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional