SUMPRODUCT Issue

  • Thread starter Thread starter Adam Lauzon
  • Start date Start date
A

Adam Lauzon

I am trying to set up a summary page from data collected
over the past few months. The 2 colums of data that I am
trying to reference are a date colum in a mm/dd/yyyy
format. The other is a a validated drop down list.

My objective is to find out how many of responses from
the drop down list occuredd in the diffente months.
Specific dates are irrelevant at this point.

Right now I am try to use a SUMPRODUCT statement and
having no luck. Mostly getting a 0 as a product.

Here is what I pretty much have so far, any help would be
great as I have been at this for a while:

=SUMPRODUCT(((Data!C2:C1000=BK2)+(Data!C2:C1000=BK3))*
(Data!A1:A1000=MONTH(x))

C colum is my data validated colum that references BK2:BK6
A colum is my date colume, I have formatted the cells to
be in the date format vs. General.
x is the Month Number i.e. 12 is December which is where
my Data starts.

Any ideas would be great.
Thanks in advance.
 
Hi
formating the date range will not help (Excel still stores the full
date value). Try the following
=SUMPRODUCT(((Data!C2:C1000=BK2)+(Data!C2:C1000=BK3)>0)*(MONTH(Data!A1:
A1000)=x))
will count all occurences for your specified month X where column B is
either BK3 or BK2
 
Adam.

From reading your response, it is difficult to know what the references to
BK2 and BK3 are for. One thing, though, in the last section of the formula
use MONTH(Data!A1:A1000)=x rather than Data!A1:A1000=MONTH(x)

Hope this helps.

Andy.
 
Change the month bit

(MONTH(Data!A1:A1000=x)

also note that if you look for January
there can be no empty cells or they will be counted
as January. If that's the case you would need

(ISNUMBER(Data!A1:A1000)*(MONTH(Data!A1:A1000=x)

that is only for January
 
Adam,

If A is a date column, don't you need to do a month on that and not x whixh
is a number

=SUMPRODUCT(((Data!C2:C1000=BK2)+(Data!C2:C1000=BK3))*(MONTH(1:A1000)=x))



HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top