I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3))))
I am using this formula in a rather large spreadsheet, A3:J48943, which is a
database logging daily activities of a Marketing Team. Column A contains date
values of the specific activity and Column C contains text values indicating
the activity type, i.e. Cold Calling, Client Visit, etc, etc.

I am trying to achieve the following: Counting the total number of
activities per week. Is my formula above correct? Somehow I doubt it because
when I run a COUNTA on Column C, I get a different value to the totals
reported per week using the above formula. Please help.

Hi,

I can't see anything wrong with the formula so if your getting unexpected
results then maybe the dates are suspect, are they really dates or text that
looks like a date.

Mike

"WildWill" wrote:

I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3))))
I am using this formula in a rather large spreadsheet, A3:J48943, which is a
database logging daily activities of a Marketing Team. Column A contains date
values of the specific activity and Column C contains text values indicating
the activity type, i.e. Cold Calling, Client Visit, etc, etc.

I am trying to achieve the following: Counting the total number of
activities per week. Is my formula above correct? Somehow I doubt it because
when I run a COUNTA on Column C, I get a different value to the totals
reported per week using the above formula. Please help.

Hi Mike

This one has me puzzled - try as I may, I keep getting a difference between
my manual calculations and the results of the formula below. Any advice?

"Mike H" wrote:

Hi,

I can't see anything wrong with the formula so if your getting unexpected
results then maybe the dates are suspect, are they really dates or text that
looks like a date.

Mike

"WildWill" wrote:

I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3))))
I am using this formula in a rather large spreadsheet, A3:J48943, which is a
database logging daily activities of a Marketing Team. Column A contains date
values of the specific activity and Column C contains text values indicating
the activity type, i.e. Cold Calling, Client Visit, etc, etc.

I am trying to achieve the following: Counting the total number of
activities per week. Is my formula above correct? Somehow I doubt it because
when I run a COUNTA on Column C, I get a different value to the totals
reported per week using the above formula. Please help.

P.S. I forgot to add: when compared to manual calculations, the formula is
wrong.

"Mike H" wrote:

Hi,

I can't see anything wrong with the formula so if your getting unexpected
results then maybe the dates are suspect, are they really dates or text that
looks like a date.

Mike

"WildWill" wrote:

I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3))))
I am using this formula in a rather large spreadsheet, A3:J48943, which is a
database logging daily activities of a Marketing Team. Column A contains date
values of the specific activity and Column C contains text values indicating
the activity type, i.e. Cold Calling, Client Visit, etc, etc.

I am trying to achieve the following: Counting the total number of
activities per week. Is my formula above correct? Somehow I doubt it because
when I run a COUNTA on Column C, I get a different value to the totals
reported per week using the above formula. Please help.

Hi,

I still think the formula is fine and you have a data issue. In a spare
column enter

=isnumber(a3)

drag down and all should evaluate as TRUE and if they don't, your dates
aren't all dates and may be text.

Mike

"WildWill" wrote:

P.S. I forgot to add: when compared to manual calculations, the formula is
wrong.

"Mike H" wrote:

Hi,

I can't see anything wrong with the formula so if your getting unexpected
results then maybe the dates are suspect, are they really dates or text that
looks like a date.

Mike

"WildWill" wrote:

I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3))))
I am using this formula in a rather large spreadsheet, A3:J48943, which is a
database logging daily activities of a Marketing Team. Column A contains date
values of the specific activity and Column C contains text values indicating
the activity type, i.e. Cold Calling, Client Visit, etc, etc.

I am trying to achieve the following: Counting the total number of
activities per week. Is my formula above correct? Somehow I doubt it because
when I run a COUNTA on Column C, I get a different value to the totals
reported per week using the above formula. Please help.

All came back "True" - can I not send you the database? (sorry if I am being
too forward - but this is an amazing situation to me)

"Mike H" wrote:

Hi,

I still think the formula is fine and you have a data issue. In a spare
column enter

=isnumber(a3)

drag down and all should evaluate as TRUE and if they don't, your dates
aren't all dates and may be text.

Mike

"WildWill" wrote:

P.S. I forgot to add: when compared to manual calculations, the formula is
wrong.

"Mike H" wrote:

Hi,

I can't see anything wrong with the formula so if your getting unexpected
results then maybe the dates are suspect, are they really dates or text that
looks like a date.

Mike

"WildWill" wrote:

I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3))))
I am using this formula in a rather large spreadsheet, A3:J48943, which is a
database logging daily activities of a Marketing Team. Column A contains date
values of the specific activity and Column C contains text values indicating
the activity type, i.e. Cold Calling, Client Visit, etc, etc.

I am trying to achieve the following: Counting the total number of
activities per week. Is my formula above correct? Somehow I doubt it because
when I run a COUNTA on Column C, I get a different value to the totals
reported per week using the above formula. Please help.

You can upload it here and post the link

http://www.savefile.com/

Mike

"WildWill" wrote:

All came back "True" - can I not send you the database? (sorry if I am being
too forward - but this is an amazing situation to me)

"Mike H" wrote:

Hi,

I still think the formula is fine and you have a data issue. In a spare
column enter

=isnumber(a3)

drag down and all should evaluate as TRUE and if they don't, your dates
aren't all dates and may be text.

Mike

"WildWill" wrote:

P.S. I forgot to add: when compared to manual calculations, the formula is
wrong.

"Mike H" wrote:

Hi,

I can't see anything wrong with the formula so if your getting unexpected
results then maybe the dates are suspect, are they really dates or text that
looks like a date.

Mike

"WildWill" wrote:

I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3))))
I am using this formula in a rather large spreadsheet, A3:J48943, which is a
database logging daily activities of a Marketing Team. Column A contains date
values of the specific activity and Column C contains text values indicating
the activity type, i.e. Cold Calling, Client Visit, etc, etc.

I am trying to achieve the following: Counting the total number of
activities per week. Is my formula above correct? Somehow I doubt it because
when I run a COUNTA on Column C, I get a different value to the totals
reported per week using the above formula. Please help.

=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3 :$A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=D ATE(2009,4,3))))

You have a misplaced parenthesis.

Try it like this:

=SUMPRODUCT(--(Input!$C$3:$C$48943<""),--(Input!$A$3:$A$48943=DATE(2009,3,30)),--(Input!$A$3:$A$48943<=DATE(2009,4,3)))

Better to use cells to hold the date criteria:

A1 = 3/30/2009
B1 = 4/3/2009

=SUMPRODUCT(--(Input!$C$3:$C$48943<""),--(Input!$A$3:$A$48943=A1),--(Input!$A$3:$A$48943<=B1))


--
Biff
Microsoft Excel MVP


"WildWill" wrote in message
...
I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3))))
I am using this formula in a rather large spreadsheet, A3:J48943, which is
a
database logging daily activities of a Marketing Team. Column A contains
date
values of the specific activity and Column C contains text values
indicating
the activity type, i.e. Cold Calling, Client Visit, etc, etc.

I am trying to achieve the following: Counting the total number of
activities per week. Is my formula above correct? Somehow I doubt it
because
when I run a COUNTA on Column C, I get a different value to the totals
reported per week using the above formula. Please help.