# Column Match and Sumproduct

#### Spockster

##### Board Regular
I have a formula that works great however the issue is that they keep moving the location of it everytime I export. What I would like to do is create within the formula a way to locate the column header then do the calculation.

Here is the formula I have;
=IF(E5<1,"0",(SUMPRODUCT(('Sheet 1'!\$M\$2:\$M\$20000=B5 )*('Sheet 1'!\$BI\$2:\$BI\$20000="In Progress"))))

I was trying to use the Match Function but must have been doing something wrong. I would keep getting the #Num Error

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Have you tried naming the formula via INSERT|NAME|DEFINE

And then referring to it in your sheet, so that if the formula is move it still refers to the name?

If that is not an option, then what determines the column the formula is in (i.e. how do you want to find the column)?

Not sure what you mean by the Name Function. I tried it and nothing happend.
What I would like to do it rather than specify Column M1:M1000 I would like the formula to search for the header which in this case is "Call Status"

Then you need the Match function, to find the column number,

=Match("Call Status",\$A\$1:\$Z\$1,0), where A1:Z1 is the potential location of the column header.

Assumptions:

On 'Sheet 1'...

C5 contains the first column header/label of interest

D5 contains the second column header/label of interest

Formula:

=SUMPRODUCT((INDEX('Sheet 1'!\$A\$2:\$BI\$20000,0,MATCH(C5,'Sheet 1'!\$A\$1:\$BI\$1,0))=B5 )*(INDEX('Sheet 1'!\$A\$2:\$BI\$20000,0,MATCH(D5,'Sheet 1'!\$A\$1:\$BI\$1,0))="In Progress"))

Hope this helps!

Replies
5
Views
148
Replies
9
Views
241
Replies
9
Views
268
Replies
17
Views
274
Replies
13
Views
348

1,203,523
Messages
6,055,895
Members
444,832
Latest member
Kauri

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back