How do you total the number of times a certain phrase entered in oneColumn has a date entered in ano

[lydiaevans7]

Hi all,
I hope someone can help me with this formula. I am trying to count the
number of times one column with a certain phrase in it has a date
entered in another column. We have been using a variation of
=SUMPRODUCT(--($D6:$D1242=D1250),--(G6:G1242=G1246))

Note: In the above formula D1250 and G1246 are linked to a key with
certain values.

We're stuck on how to narrow down the count to only instances when a
certain phrase is in one column and a date (nonspecific but in month/
day/year format) is in another column?

Example: If "Ferry ride" is in Column A and there is a date is Column
C, how do you total only those instances?

I appreciate any and all suggestions, thanks in advance!
-Lydia

 

[JE McGimpsey]

Example: If "Ferry ride" is in Column A and there is a date is Column
C, how do you total only those instances?

I appreciate any and all suggestions, thanks in advance!

Dates in XL are just integer offsets from a base date, so in the Mac
default date system, 0 represents 1/1/1904, while today (8 January 2008)
is represented by 37993 (i.e., the number of days elapsed since
1/1/1904).

So depending on your date range, you may be able to use something like

=SUMPRODUCT(--(A1:A1000="Ferry Ride"), --(C1:C1000>=DATE(2000,1,1)),
--(C1:C1000<=DATE(9999,12,31)))

This assumes that column C doesn't contain values that would correspond
to values in the date range.

 

[lydiaevans7]

Dates in XL are just integer offsets from a base date, so in the Mac
default date system, 0 represents 1/1/1904, while today (8 January 2008)
is represented by 37993 (i.e., the number of days elapsed since
1/1/1904).

So depending on your date range, you may be able to use something like

=SUMPRODUCT(--(A1:A1000="Ferry Ride"), --(C1:C1000>=DATE(2000,1,1)),
--(C1:C1000<=DATE(9999,12,31)))

This assumes that column C doesn't contain values that would correspond
to values in the date range.

Thank you! We got it to work!! I appreciate both of your suggestions,
as you know with Excel it takes some trial and error to find the best
way to do things.