Help with Countifs formula please

[sam77]

Hi All,


Could anyone help in writing a countifs formula by matching the headings. For ex: The target cell in which im writing the countifs should be able to execute the countifs function by searching for that particular column. Im not sure if im clear. Its more like.. in a table find this column with the heading "Name" and then execute countifs based on additional criteria...Please do let me know if im not clear with my question.


Thanks a ton!

Regards,

Sam

 

[fred]

you don't need the header to be part of the criteria. just use the countif and define the criteria from the data itself.

 

[sam77]

Hey Fred...


What im anticipating is that if the column position changes.. then what im looking at is in the changed table , i would need to first find the column with that particular heading and if it matches then execute the "countifs".. i understand that countif would work as per what you have mentioned .... but im looking for countifs and this would make it i guess a lil more dynamic... this could be coded in a macro,.. but i dont really want to do that if i can do it via a formula...


Regards,

Sam

 

[fred]

try


sumproduct((header=header choice)*(data table = data choice)*data table)

 

[fred]

i.e. name your header row as "Header". Then pick a blank cell to be the input cell for the intended header. Since the data table may expand/contract and you don't know exactly the location of the targeted header after each refresh of data.


Name your data table as "Data_table". Then pick a blank cell to be the criteria for input.

 

[sam77]

thanks Fred.. will definitely try this...