Variable criteria formula
 

Column 'A' contains 2-digit codes (i.e. LB, BA, BB, etc.).
Column 'C' contains dates (mm/dd/yy format)
Column 'L' contains mileage figures.

Attempting to write a formula that will calculate mileage (Col. L) by a
specific code (Col. A) and a certain date (Col. C).

For example, total mileage for all 2005 dates that have code "LB".

This is probably simpler than I am making it out to be. Any help is
appreciated!

Variable criteria formula
 

Hi!

Use 2 cells to hold the criteria:

M1 = LB
N1 = year = 2005

=SUMPRODUCT(--(A1:A15=M1),--(YEAR(C1:C15)=N1),L1:L15)

Biff

"Brent" wrote in message
...
Column 'A' contains 2-digit codes (i.e. LB, BA, BB, etc.).
Column 'C' contains dates (mm/dd/yy format)
Column 'L' contains mileage figures.

Attempting to write a formula that will calculate mileage (Col. L) by a
specific code (Col. A) and a certain date (Col. C).

For example, total mileage for all 2005 dates that have code "LB".

This is probably simpler than I am making it out to be. Any help is
appreciated!

Variable criteria formula
 

Brent,

Try:
=SUM((A1:A99="LB")*(YEAR(C1:C99)=2005)*(L1:L99))
where your data is in rows 1 through 99

For each total, you may then replace the code and/or year (or refer to other
cells that contain these values)

NB: This formula must be array-entered (ctrl-shift-enter)

HTH,
Ryan

"Brent" wrote:

Column 'A' contains 2-digit codes (i.e. LB, BA, BB, etc.).
Column 'C' contains dates (mm/dd/yy format)
Column 'L' contains mileage figures.

Attempting to write a formula that will calculate mileage (Col. L) by a
specific code (Col. A) and a certain date (Col. C).

For example, total mileage for all 2005 dates that have code "LB".

This is probably simpler than I am making it out to be. Any help is
appreciated!

Variable criteria formula
 

I have tried both formulas and cannot seem to get either to equate. At best
they simply return a "0" value.
Sorry to be so cumbersome....





"Ryan Poth" wrote:

Brent,

Try:
=SUM((A1:A99="LB")*(YEAR(C1:C99)=2005)*(L1:L99))
where your data is in rows 1 through 99

For each total, you may then replace the code and/or year (or refer to other
cells that contain these values)

NB: This formula must be array-entered (ctrl-shift-enter)

HTH,
Ryan

"Brent" wrote:

Column 'A' contains 2-digit codes (i.e. LB, BA, BB, etc.).
Column 'C' contains dates (mm/dd/yy format)
Column 'L' contains mileage figures.

Attempting to write a formula that will calculate mileage (Col. L) by a
specific code (Col. A) and a certain date (Col. C).

For example, total mileage for all 2005 dates that have code "LB".

This is probably simpler than I am making it out to be. Any help is
appreciated!

Variable criteria formula
 

Hi!

Well, then you have to check your data and make sure things are really what
they're supposed to be.

For example, are the dates really dates and not just TEXT strings that look
like dates?

True Excel dates are actually numbers that are formatted to look like a
date.

Are the mileage values really numeric numbers or are they TEXT numbers?

Are there any unseen spaces in column A? For example:

LB might actually be:

<spaceLB
LB<space

Just some things to look at that can cause problems.

Biff

"Brent" wrote in message
...
I have tried both formulas and cannot seem to get either to equate. At
best
they simply return a "0" value.
Sorry to be so cumbersome....





"Ryan Poth" wrote:

Brent,

Try:
=SUM((A1:A99="LB")*(YEAR(C1:C99)=2005)*(L1:L99))
where your data is in rows 1 through 99

For each total, you may then replace the code and/or year (or refer to
other
cells that contain these values)

NB: This formula must be array-entered (ctrl-shift-enter)

HTH,
Ryan

"Brent" wrote:

Column 'A' contains 2-digit codes (i.e. LB, BA, BB, etc.).
Column 'C' contains dates (mm/dd/yy format)
Column 'L' contains mileage figures.

Attempting to write a formula that will calculate mileage (Col. L) by a
specific code (Col. A) and a certain date (Col. C).

For example, total mileage for all 2005 dates that have code "LB".

This is probably simpler than I am making it out to be. Any help is
appreciated!