Column Match and Sumproduct

[Spockster]

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

 

[NBVC]

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)?

 

[Spockster]

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"

 

[NBVC]

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.

 

[Domenic]

Assumptions:

On 'Sheet 1'...

A1:BI1 contains your headers/labels

A2:BI20000 contains your data

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"))

Adjust the ranges accordingly.


Hope this helps!