SUMPRODUCT to find occurences within date range

W

WildWill

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<=DATE(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.
 
M

Mike H

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
 
W

WildWill

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?
 
M

Mike H

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
 
W

WildWill

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)
 
T

T. Valko

=SUMPRODUCT((Input!$C$3:$C$48943<>"")*(Input!$A$3:$A$48943>=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DATE(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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct formula 3
counting cells that fall within a certain date range 9
Sumproduct and Count If 1
sumproduct with date range 4
SUMPRODUCT Problem 2
Date Format and Sumproduct 9
SUMPRODUCT to calculate unique occurences of string in column of d 3
SUMPRODUCT with Date? 3

Top