Variable criteria formula

[Guest]

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!

 

[Biff]

Hi!

Use 2 cells to hold the criteria:

M1 = LB
N1 = year = 2005

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

Biff

 

[Guest]

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

 

[Guest]

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....

 

[Biff]

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:

<space>LB
LB<space>

Just some things to look at that can cause problems.

Biff