#### Dynamically decide column in sumif formula

```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
```
 0
6/30/2008 2:41:22 AM
excel 39879 articles. 2 followers.

6 Replies
694 Views

Similar Articles

[PageSpeed] 58

```One way

D6 = A
E6 = Sales

Then in F6:
=SUMIF(Sheet2!A2:A46,D6,OFFSET(Sheet2!A2:A46,,MATCH(E6,Sheet2!A1:D1,0)-1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"AC" <andrewfreestuff@gmail.com> wrote in message
> 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

```
 0
demechanik (4694)
6/30/2008 3:13:53 AM
```The earlier presumes that Sheet2's A1:D1 contains the col headers:
>> StockNo, Date, Sales, Boxes
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

```
 0
demechanik (4694)
6/30/2008 3:24:21 AM
```Try one of these:

A1 = StockNo
B1 = category (Sales, Boxes)

=SUMIF(Sheet2!A2:A46,A1,INDEX(Sheet2!B1:D46,,MATCH(B1,Sheet2!B1:D1,0)))

=SUMPRODUCT((Sheet2!A2:A46=A1)*(Sheet2!B1:D1=B1),Sheet2!B2:D46)

--
Biff
Microsoft Excel MVP

"AC" <andrewfreestuff@gmail.com> wrote in message
> 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

```
 0
biffinpitt (3172)
6/30/2008 4:57:09 AM
```Clarification:

> A1 = StockNo

Should be:

A1 = StockNo (A, B)

--
Biff
Microsoft Excel MVP

"T. Valko" <biffinpitt@comcast.net> wrote in message
news:OtfNC3m2IHA.4772@TK2MSFTNGP03.phx.gbl...
> Try one of these:
>
> A1 = StockNo
> B1 = category (Sales, Boxes)
>
> =SUMIF(Sheet2!A2:A46,A1,INDEX(Sheet2!B1:D46,,MATCH(B1,Sheet2!B1:D1,0)))
>
> =SUMPRODUCT((Sheet2!A2:A46=A1)*(Sheet2!B1:D1=B1),Sheet2!B2:D46)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "AC" <andrewfreestuff@gmail.com> wrote in message
>> 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
>
>

```
 0
biffinpitt (3172)
6/30/2008 5:08:55 AM
```Supposing criterias are : H1 = B & H2 = Sale

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

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.)
dilipandey@yahoo.com
dilipandey@gmail.com
New Delhi, India

"AC" wrote:

> 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
>
```
 0
DILipandey (41)
6/30/2008 12:44:01 PM
```You can eliminate the 2nd call to ADDRESS:

--
Biff
Microsoft Excel MVP

"DILipandey" <DILipandey@discussions.microsoft.com> wrote in message
> Supposing criterias are : H1 = B & H2 = Sale
>
> and your data is in range A1:D5, you can use the following formula:-
>
>
> 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.)
> dilipandey@yahoo.com
> dilipandey@gmail.com
> New Delhi, India
>
>
> "AC" wrote:
>
>> 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
>>

```
 0
biffinpitt (3172)
6/30/2008 4:48:04 PM

Similar Artilces:

I am linking between two sheets and the link formula is showing up instead the answer. It could be a few things... #1. The cell is formatted as text. Format|cells|General (or some number variation--just not Text) then hit F2 followed by enter to re-enter the formula #2. You're looking at formulas Tools|options|View tab\ Make sure Formulas isn't checked #3. Maybe a mistake in your formula. Make sure that there is no space before the initial = (equal sign) BalancedSolutions wrote: > > I am linking between two sheets and the link formula is showi...

How do I designate an entry as the column head/label/name vs. data entry? In what context? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "4most" <4most@discussions.microsoft.com> wrote in message news:B5B96664-1D46-4CD8-8564-5ABDBF36DA64@microsoft.com... > How do I designate an entry as the column head/label/name vs. > data entry? I am creating an elementary spreadsheet, and I want to place headings at the top of each column. "Chip Pearson" wrote: > In what context? > > > -- > ...

Screen updating and formulas
With Excel and VBA, as soon as you add a formula to a cell this updates the screen. Does anyone know a way around this? E.g.. with Application .Screenupdating = False .range("a1").Formula ="=sum(a2:a4)" .range("b1").Formula ="=sum(b2:b4)" In the example above the screen updates after adding in the formula to cell "A1" Any ideas from anyone would be appreciated. "John" <jj@hotmail.com> wrote... >With Excel and VBA, as soon as you add a formula to a cell this updates the >screen. Does anyone know a way ar...

Two-Column problem
Using Arrange, Layout Guides, I set up Publisher 2002 with two columns per page and with two master pages with mirrored guides. For the first 11 pages, text flowed as expected. That is, each new pair of pages had two textboxes per page. But now, when I add a pair of pages, Publisher gives me guides for two columns per page but gives me only one page-wide textbox per page, not two. How do I force Publisher to give me two textboxes per page, as it did for the first 11 pages? (I might be missing something obvious. I haven't used Publisher in several years.) Thanks. Charley After mana...

when displaying formulas, how to start a new line in the same cel.
In the displaying formulas mode, if the formula is too long, how to start a new line in the same cell so that the formulas can be printed in one page. (Alt + enter won't work in formula mode) Hi AFAIK the only thing you could do is copy the formula as 'Text' (e.g. put an apostrophe in front of the formula) and then use aLT+ENTER -- Regards Frank Kabel Frankfurt, Germany "EL" <EL@discussions.microsoft.com> schrieb im Newsbeitrag news:0ADE2051-6D82-439C-B65C-C9B7E0B9A925@microsoft.com... > In the displaying formulas mode, if the formula is too long, how to sta...

Trying to write a formula that changes which worksheet to used
I am trying to write a formula that uses a different worksheet in the current workbook depending on the value from a lookup result and/or the direct entry of a worksheet name in one cell. For instance, if my lookup result is November, I want to pull information from cells on the November worksheet, if December, information from the December worksheet, etc. This way, all I have to do is change the lookup value and/or the entry to get different results based on the appropriate worksheet. This is useful in repetitive activities using the same layout but different numbers. Any help would...

Transferring a two column excel file into outlook
Is there a simple way to transfer an excel file which has two columns ,one for names the other for phone numbers, into my outlook address book? I have a excel file with over a hundred such entries and would rather not enter them individually, surly there is a simple way to do this. Any suggestions? Condo <theCONDOmanager@gmail.com> wrote: > Is there a simple way to transfer an excel file which has two > columns ,one for names the other for phone numbers, into my outlook > address book? I have a excel file with over a hundred such entries > and would rather not enter them in...

if statement formula
I want to create a cell that takes a total, and multplies it by 15%, however, if the 15% is less than twenty, I want the amount to be 20. I.E.-- subtotal is \$95.00, so 15% would be \$14.25. However I want the cell to show\$20.00. I.E.-- sutotal is \$300.00, so 15% would be \$45.00. I want the cell to show the \$45.00. Thank you If your total is in cell A1 then in B1 type =IF(A1*0.15<20,20,A1*0.15) Regards Rowan "Marlis" wrote: > I want to create a cell that takes a total, and multplies it by 15%, however, > if the 15% is less than twenty, I want the amount to be 20....

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Blank column to be unique increment
Hi Everyone, I'm trying to write an update SQL to fill an Integer column with an incremental number. Example: My table has 500 rows, I want this new Integer column to be filled with number of 1 to 500. Is there a way to do this via an update SQL statement? Thanks in advance. Here is one example: CREATE TABLE Foo ( keycol INT NOT NULL PRIMARY KEY, datacol CHAR(1), seq_nbr INT); INSERT INTO Foo (keycol, datacol) VALUES (1, 'a'); INSERT INTO Foo (keycol, datacol) VALUES (8, 'b'); INSERT INTO Foo (keycol, datacol) VALUES (11, 'c'); INS...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

if / or formulas
i build a spreadhseet daily of portfolio positions and the percentage that each stock takes of the total portfolio value......the individual positions are cut and paste from another system striaght to excel.....at the end of this vertical list of positions I add a 'total' amount. Obviously the number of positions changes on a daily basis. what I also want to add is a table that says if any one single stock is over three percent of the portfolio value, so i need formula(e) that looks down the list and tells me if the number is greater than three. However the range of cells wil...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

Using Formulas with Charts
I have a chart that references cells with the following formula =if(a1="","",a1/a2). The formula is used for each month Jan-Dec. I would like to copy the formula across all months. When I do this my data point show as zero for months that have not happened yet. What can I do to not show a line on the chart for a data point with a "" or null value? Thank you, Sandy The cell with the formula isn't blank, and "" does not produce a blank. There is not a formulaic way to simulate a blank, but for many cases, a line chart or XY chart will treat...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

vlookup first 4 characters only in a column
Hi Any help with the following would be greatly appreciated - I'm trying to do a vlookup whereby it only searches/has to match the first 4 characters in the utmost left column of the table and return the associated cell. My non working formula is: =VLOOKUP(A:A(left4),EmailList!A:C,3,FALSE) whereby the A:A(left4) is meant to be the first 4 characters from the left of the column. Thks Gus Try this: =VLOOKUP(LEFT(A1,4),EmailList!A:C,3,FALSE) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============...

OLE DB provider cannot connect to Excel 2007 with 400 columns
I use a TADOConnection (e.g. connection1) to connect a Excel 2007 file with about 400 columns in a worksheet, using the following connectionString: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\temp\exportFG.xlsx;Extended Properties="Excel 12.0;HDR=YES;" when I set the connected properties to true, no error occur. but when i put a TADOTable and set its connection to connection1, tableDirect = true, only 256 columns can be retrieved!! is this a limitation of the OLE DB provider for Excel? (I know before Excel 2007, a worksheet has maximum 256 columns only) ...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...

Need a method to Dynamicly change the X axis Scale on a chart. #2
I dynamically change the amount of data on a chart. This causes the X axis Scale to either to either show too few values or too many values ( a solid black line if too many). I would like a method to dynamically change the scale values in relation to the data span. Joe - Do you want to limit the number of points displayed? http://peltiertech.com/Excel/Charts/Dynamics.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Or do you want to link the axis scales to something you calculate in the sheet? http://peltiertech.com/Excel/Zips/SwitchXYAxes.zip - Jon ----...

column width #3
Hello, We have a small networking. (1 Windows 2K server and 4 Windows 98 workstations) We use Office 2K, three workstations print same Excel documents are ok. Only one computer print the column width is wider than others. i.e. same excel document in server Column width is 3 inch, the print out is about 3.5 inch. the other 3 computers print 3 inch. Row height is 1 inch, all 4 computers print 1 inch. Only the column is different. I tried to uninstall and install again, nothing help. Thanks David Column width is set as a multiple of the width required to display one numeric character in t...

Dynamically populate a drop down list
Hi, I'm trying to dynamically populate a drop down list in MS CRM (1.2) based on certain parameters. I suppose I could do it using a SQL stored procedure, but I would prefer to use standard/supported CRM customizations. Any assistance in this would be greatly appreciated. Thanks! -Dave- Dave, You can do it in 3.0 pretty easily based upon an example in the SDK... Here are some pretty pictures... http://blogs.msdn.com/midatlanticcrm/archive/2005/12/04/499868.aspx I have not tried it with 1.2, but it could probably work... Thanks Ben "Dave" <renor321@yahoo.com> wrot...

Dynamics GP 10 and Dynamics AX 4.0
just a half year ago, our company has started to work with Dynamics AX 4.0. Now i am doing some programming in X++ and almost customizing reports. Accidently, i stumpled on Dynamics GP 10.0 and i am wondering what s the difference between AX and GP? What stands GP for? Maybe GP is to replace AX, and our company backs to wrong horse? Does GP 10 contains AX 4.0? I have learnt lot of X++ programming, would it be possible to using X++ in the GT 10 field, too? Great Plains (GP) is another ERP system that Microsoft acquired a few years ago when it acquired the company of the same name. GP ...

Formula to calculate elapsed time between certain dates and times
Hi I am trying to calculate the elspased time between certain dates and times with a result in total hours. The catch is that I only want the formula to inlcude times during business hours (Monday to Friday, 8:30am - 5:30pm) E.g (17/12/2004 10:07) - (3/02/2005 15:22) = ??? Is this possible with excel? Many thanks Hi! Do you want to exclude any holidays that fall on regular business days? A1 = 17/12/2004 10:07 B1 = 3/02/2005 15:22 =SUM(17.5/24-MOD(A1,INT(A1)),MOD(B1,INT(B1))-8.5/24)+ (NETWORKDAYS(A1,B1)-2)*9/24 Format the cell as [h]:mm. If you want to exclude holidays you need to...