Dynamically decide column in sumif formula

[AC]

Hi

I am going to demo my question with a simple example

I have the following data:

StockNo, Date, Sales, Boxes
A xx 100 10
B xx 150 9
A xx 200 5
A xx 50 5

On another worksheet I want to find the sum of A sales. I type "A"
into cell D6 as my criteria, and I have the following sumif formula:

=SUMIF(Sheet2!A2:A46,D6,Sheet2!C2:C46)

What this is doing is summing all the "C" column figures (sales) based
on the if, so it sums the sales for stock "A".


What I want to do is dynamically decide which column to sum based on
some other criteria, for example I want to also enter "Sales" into a
cell as well as "A" and have it know that is column C (sales) that I
want to sum. Maybe I would then change the entry to "Boxes" and it
would know that the sumif should change to summing column D and not C.
I want it to be flexible to what columns the data is in and not be
hardcoded, so if my data turned up in a different format, eg boxes
listed before sales, then it will all work.

I have seen the formulas Index/Match etc and I can use those to figure
out the column number (ie sales = 3rd column, Boxes = 4th column) but
I am stuck on the next step as to how to use this column number in the
last sumif part of the formula.

All help appreciated.
Thanks
Andy C

 

[Max]

One way

D6 = A
E6 = Sales

Then in F6:
=SUMIF(Sheet2!A2:A46,D6,OFFSET(Sheet2!A2:A46,,MATCH(E6,Sheet2!A11,0)-1))

 

[Max]

The earlier presumes that Sheet2's A11 contains the col headers:

 

[T. Valko]

Try one of these:

A1 = StockNo
B1 = category (Sales, Boxes)

=SUMIF(Sheet2!A2:A46,A1,INDEX(Sheet2!B146,,MATCH(B1,Sheet2!B11,0)))

=SUMPRODUCT((Sheet2!A2:A46=A1)*(Sheet2!B11=B1),Sheet2!B246)

 

[T. Valko]

Clarification:

A1 = StockNo

Should be:

A1 = StockNo (A, B)

 

[DILipandey]

Supposing criterias are : H1 = B & H2 = Sale

and your data is in range A15, you can use the following formula:-

=SUMIF($A$1:$A$6,$H$1,INDIRECT(ADDRESS(1,MATCH($H$2,$A$1:$D$1,0))&":"&ADDRESS(1,MATCH($H$2,$A$1:$D$1,0))))

Whenever you change the criteria in H1 or H2, it will automatically gives
out the desired result.

Please let me know, if it works. Thanks

--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[T. Valko]

You can eliminate the 2nd call to ADDRESS:

=SUMIF($A$1:$A$6,$H$1,INDIRECT(ADDRESS(1,MATCH($H$2,$A$1:$D$1,0))))