need a formula, please~

[thereseexceljakubiak]

i have 40 columns across my sheet. columns 1-15 are simple fields...columns 16-40 are fields with dates in them. What formula would be best to search across the column headers and find a text then do countifs if the dates within that specific column are greater than or equal to TODAY?


instead of searching for the column, manually, selecting it an locking it into the formula... i have to create several hundred formulas based on many filters. i just want to be able to type either "column17" or "column20", the text of the column header and formula should scan across the first row, find my text (the column header) then search and COUNT in that column for dates greater than or equal to today.

 

[aamirsq]

uploading a sample file will lead you to faster solution...


If you need help in posting a sample file?


Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[jason]

while reading the first paragraph i was thinking a nested if function..... but then i read the second part and you lost me

 

[DaveTurton]

Search criteria in cell AO1


below formula in cell AO2

=COUNT(IF(P1:AN1=AO1,IF(P2:AN6000>=TODAY(),1)))

Make due you press Ctrl + Shift + Enter


It will search across the headers of columns 16-40 to see which match the search criteria in AO1 then count the numebr of dates in that column (up to row 6000) that are greater or equal to today

 

[thereseexceljakubiak]

hi sorry: here is the link to the workbook:


http://dl.dropbox.com/u/35552808/Book1.xlsx

 

[NARAYANK991]

Hi Therese ,


For formula 1 , try this :


=SUMPRODUCT(((OFFSET(DATA!$A$1:$A$7,,MATCH(C1,DATA!$A$1:$AN$1,0)-1)=D1)*(OFFSET(DATA!$A$1:$A$7,,MATCH(C2,DATA!$A$1:$AN$1,0)-1)=D2)*(OFFSET(DATA!$A$1:$A$7,,MATCH(C3,DATA!$A$1:$AN$1,0)-1)=D3)),--((OFFSET(DATA!$A$1:$A$7,,MATCH(I1,DATA!$A$1:$AN$1,0)-1)>=K1)))


BTW , you need to correct the data in column C to reflect the headers correctly ; Header2 will not match 'Header 2' ( without the single quotes ).


Narayan

 

[thereseexceljakubiak]

Thank you Narayan, i will try the formula and let you know! thank you sooo much!

 

[thereseexceljakubiak]

That works!! THANK YOU NARAYAN!!!