Using Column in SumIf Found by Match

[Charles2019]

I have used the MATCH function to return the column number in which data is found that I want to use in a SUMIF function. What is the next step?

 

[Marcelo Branco]

Welcome to Mr Excel forum

Could you provide an example? A small data sample (~5 to 10 rows) along with expected result would be helpful.

M.

 

[DanteAmor]

I attach a couple of examples with match and sumproduct, I hope you help.


<br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:25.66px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; text-align:center; ">NAME</td><td style="font-weight:bold; text-align:center; ">COL B</td><td style="font-weight:bold; text-align:center; ">COL C</td><td style="font-weight:bold; text-align:center; ">COL D</td><td style="font-weight:bold; text-align:center; ">COL E</td><td style="font-weight:bold; "> </td><td style="font-weight:bold; text-align:center; ">NAME</td><td style="font-weight:bold; text-align:center; ">COL</td><td style="font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >a</td><td style="text-align:right; ">1</td><td style="text-align:right; ">41</td><td style="text-align:right; ">86</td><td style="text-align:right; ">100</td><td > </td><td >b</td><td >COL D</td><td style="background-color:#ffff00; text-align:right; ">177</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >a</td><td style="text-align:right; ">2</td><td style="text-align:right; ">42</td><td style="text-align:right; ">87</td><td style="text-align:right; ">101</td><td > </td><td >c</td><td >COL E</td><td style="background-color:#92d050; text-align:right; ">209</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >b</td><td style="text-align:right; ">3</td><td style="text-align:right; ">43</td><td style="background-color:#ffff00; text-align:right; ">88</td><td style="text-align:right; ">102</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >b</td><td style="text-align:right; ">4</td><td style="text-align:right; ">44</td><td style="background-color:#ffff00; text-align:right; ">89</td><td style="text-align:right; ">103</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >c</td><td style="text-align:right; ">5</td><td style="text-align:right; ">45</td><td style="text-align:right; ">90</td><td style="background-color:#92d050; text-align:right; ">104</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >c</td><td style="text-align:right; ">6</td><td style="text-align:right; ">46</td><td style="text-align:right; ">91</td><td style="background-color:#92d050; text-align:right; ">105</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I2</td><td >=SUMPRODUCT((A2:A7=G2)*(B1:E1=H2)*(B2:E7))</td></tr><tr><td >I3</td><td >=SUMIF(A2:A7,G3,OFFSET(A2:A7,,MATCH(H3,B1:E1,0)))</td></tr></table></td></tr></table>

 

[Marcelo Branco]

Using data sample provided by DanteAmor (post 3)

Another possible formula
I2 copied down
=SUMIF(A$2:A$7,G2,INDEX(B$2:E$7,0,MATCH(H2,B$1:E$1,0)))

M.

 

[DanteAmor]

I show you 2 more options, including the option of marcelo.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:25.66px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:78.89px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; text-align:center; ">NAME</td><td style="font-weight:bold; text-align:center; ">COL B</td><td style="font-weight:bold; text-align:center; ">COL C</td><td style="font-weight:bold; text-align:center; ">COL D</td><td style="font-weight:bold; text-align:center; ">COL E</td><td style="font-weight:bold; "> </td><td style="font-weight:bold; text-align:center; ">NAME</td><td style="font-weight:bold; text-align:center; ">COL</td><td style="font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >a</td><td style="text-align:right; ">1</td><td style="background-color:#00b0f0; text-align:right; ">41</td><td style="text-align:right; ">86</td><td style="text-align:right; ">100</td><td > </td><td >b</td><td >COL D</td><td style="background-color:#ffff00; text-align:right; ">177</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >a</td><td style="text-align:right; ">2</td><td style="background-color:#00b0f0; text-align:right; ">42</td><td style="text-align:right; ">87</td><td style="text-align:right; ">101</td><td > </td><td >c</td><td >COL E</td><td style="background-color:#92d050; text-align:right; ">209</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >b</td><td style="background-color:#ffc000; text-align:right; ">3</td><td style="text-align:right; ">43</td><td style="background-color:#ffff00; text-align:right; ">88</td><td style="text-align:right; ">102</td><td > </td><td >a</td><td >COL C</td><td style="background-color:#00b0f0; text-align:right; ">83</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >b</td><td style="background-color:#ffc000; text-align:right; ">4</td><td style="text-align:right; ">44</td><td style="background-color:#ffff00; text-align:right; ">89</td><td style="text-align:right; ">103</td><td > </td><td >b</td><td >COL B</td><td style="background-color:#ffc000; text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >c</td><td style="text-align:right; ">5</td><td style="background-color:#b2a1c7; text-align:right; ">45</td><td style="text-align:right; ">90</td><td style="background-color:#92d050; text-align:right; ">104</td><td > </td><td >c</td><td >COL C</td><td style="background-color:#b2a1c7; text-align:right; ">91</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >c</td><td style="text-align:right; ">6</td><td style="background-color:#b2a1c7; text-align:right; ">46</td><td style="text-align:right; ">91</td><td style="background-color:#92d050; text-align:right; ">105</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I2</td><td >=SUMPRODUCT((A2:A7=G2)*(B1:E1=H2)*(B2:E7))</td></tr><tr><td >I3</td><td >=SUMIF(A2:A7,G3,OFFSET(A2:A7,,MATCH(H3,B1:E1,0)))</td></tr><tr><td >I4</td><td >=SUMIF(A$2:A$7,G4,INDEX(B$2:E$7,0,MATCH(H4,B$1:E$1,0)))</td></tr><tr><td >I5</td><td >{=SUM(IF(A2:A7=G5,IF(B1:E1=H5,B2:E7)))}</td></tr><tr><td >I6</td><td >=SUMPRODUCT((A2:A7=G6)*(COLUMN(B1:E1)=MATCH(H6,A1:E1,0))*(B2:E7))</td></tr></table></td></tr></table>


Note:
The formula in I5 is an array formula.

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Charles2019]

It would be best if I could attach an extract from the file I'm working on, but I don't know how to do that. In a nutshell, I have a workbook with two sheets. The first is a table of various construction jobs with columns for job names, budgets and and actual costs. My hope is to populate the actual costs with data exported from our accounting system in Excel format into the second sheet. The second sheet is accounting data with the first column being general ledger numbers and then columns for actual costs for each job. The jobs data exported will change from month to month as new jobs are added and old jobs are completed so I can't cite the specific columns in a SUMIF function. I have added a column to the left of the GL data to map the GL numbers to summary categories. Since the number of active jobs and the active names will change routinely, I'm trying to create a formula in the actual columns in the first sheet which will perform a SUMIF with the GL category name from the appropriate column head as the criteria and the actual job cost column as the sum range. My first step was to use the MATCH function to generate a column number based on the job name. But, I'm stumped when it comes to converting the column number returned by the MATCH function to a column reference that can be used by the SUMIF function. Any thoughts?

 

[DanteAmor]

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Charles2019]

Here is a sample file:
https://www.dropbox.com/s/a5j1h3aacw0wa93/MrExcel.xlsx?dl=0

Note in particular, column AQ on the sheet "Job Status". The job ABCD has the match function which picks up the column number I'd like to use in the SUMIF function. The job IJKL has the SUMIF function which referenced the appropriate column directly and returns the number I want.

Thanks!

 

[DanteAmor]

Here is a sample file:
https://www.dropbox.com/s/a5j1h3aacw0wa93/MrExcel.xlsx?dl=0

Note in particular, column AQ on the sheet "Job Status". The job ABCD has the match function which picks up the column number I'd like to use in the SUMIF function. The job IJKL has the SUMIF function which referenced the appropriate column directly and returns the number I want.

Thanks!

You must create the names in the QB Data sheet:
QB_A ("A3: A57")
QB_1 ("I1: L1")
QB_Area ("I3: L57")



On sheet "Job status" in Cell AQ6


=SUMPRODUCT((QB_A=Table1[[#Headers],[Mat''l Cost to Date]])*(QB_1=Table1[[#This Row],[HS/QB Job Name]])*(QB_Area))

File test:

https://www.dropbox.com/s/jtsftp5grx8pmb3/MrExcel dam.xlsx?dl=0

 

[Charles2019]

That's what I want to avoid. The intent is to overlay the accounting information with new data each week. Consequently, the column relating to the specific jobs will change every week. What I want is to have the expression built on the SUMIF function determine the column to use as the sum range. The match function determines the column number - I need a way to convert that into a column reference that can be used by the SUMIF function.