Excel Formula Problem?

[gjmac2]

Having problem with an Excel formula.

I have a worksheet I have named 12-25.

There are two columns that I need to compare from, which are b2:b54, wich is a date column, and d2:d54, which is a text column.

On a seperate worksheet, I am trying to count how many times a given value in d2:d54 falls within a date range in b2:b54. I have tried an =IF(AND function, but cannot get Access to tabulate what I am looking for. Here is an example of the formula I am trying to use:

=COUNT(IF(AND('12-25'!b2:b54>"12/31/2007"+0,'12-25'!b2:b54<"02/01/2008"+0,'12-25'!d2:d54="ABC"),'12-25'!A2:A54))

Any help would be greatly appreciated.

Thanks

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT(('12-25'!B2:B54>"12/1/2007"+0)*('12-25'!B2:B54<"2/1/2008"+0)*('12-25'!D254="ABC")*('12-25'!A2:A54))
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gjmac2]

I appreciate the reply, and I tried your formula. However, instead of counting the instances, it adds the the values from column A. Is there anyway I can get a count of the instances from column A?

 

[SkipVought]

BTW, I perfer to use Named Ranges. 1) the formula is much more understandable 2) there is less danger of propogating an incorrect range reference (which should be ABSOLUTE,ABSOLUTE)

I also prefer referencing values rather than hard-coding values, like the From & Thru dates and the string.

Assuming that From, Thru and string are in A1:C1 and that the 4 columns ranges are named MySumValues, MyDates, MyStrings...
[tt]
=SUMPRODUCT((MyDates>A1)*(MyDates<B1)*(MyStrings=C1)*(MySumValues))
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

If you are simply COUNTING...
[tt]
=SUMPRODUCT(--('12-25'!B2:B54>"12/1/2007"+0)*('12-25'!B2:B54<"2/1/2008"+0)*('12-25'!D254="ABC"))
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

If you are merely counting, why would you include ANYTHING from column A?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[gjmac2]

That worked. Do not know what I was thinking about counting colum A. Thank you for your help.