Simplify formula help

[welshman010]

I am currently using this formula across multiple sheets

=COUNTIF($L4:$AM4,O$47)*O$48+COUNTIF($L4:$AM4,P$47)*P$48+COUNTIF($L4:$AM4,Q$47).............

On each sheet the range will change based on certain dates eg next sheet maybe from from $L4:$AG4. at moment I manually changing columns in the formula for each sheet I create, range will always be on row 4.

Is there a way if I enter dates in 2 cell say a1 b1 the formula will automatically select range between those dates.

Thanks

Kelvin

[Ron Coderre]

Which version of Excel will you be using?

[welshman010]

Multiple people will be using file. It would range across 2007,2010 and 2013 versions

[Ron Coderre]

Ok...It's helpful to know that everyone will be using a "ribbon" version of Excel.
Next, can you post a sample workbook with a couple representative sheet scenarios and the results you want to see (based on those sheets)?

[Jonmo1]

Perhaps

=SUMPRODUCT(COUNTIF($L4:$AM4,O$47:AP$47)*O$48:AP$48))

[welshman010]

Will do later tonight.