SumProduct Question

This what I am looking to do. My data source is a SQL database.

I have a query with three fields. I want to get counts on three criteria, I
have the two working find, now need the date.
=SUMPRODUCT((Sheet1!$A$2:$A$10000 = $A6) * (Sheet1!$D$2:$D$10000 = "EMAIL"))
This works fine.

The other field from Sheet1 that I want to check is a date field in the
following format.

6/15/2005 9:13


I want my report to be monthly, so I would like it check for $A6 and EMAIL
above and in my example, month of June data only.
Something like Month(data from Sheet1) = 6. I really don't want to use a
helper column if not necessary.

=SUMPRODUCT((Sheet1!$A$2:$A$10000 = $A6) * (Sheet1!$D$2:$D$10000 = "EMAIL")
* (Sheet1$C$2:$C$1000 = MONTH( not sure after this)

Thanks

=SUMPRODUCT(--(Sheet1!$A$2:$A$10000 = $A6),--(Sheet1!$D$2:$D$10000 =
"EMAIL"),--(TEXT(Sheet1$C$2:$C$1000,"mmm")="Jun"))


--
HTH

Bob Phillips

"syrac" <[email protected]> wrote in message
news:%[email protected]...
> This what I am looking to do. My data source is a SQL database.
>
> I have a query with three fields. I want to get counts on three criteria,
I
> have the two working find, now need the date.
> =SUMPRODUCT((Sheet1!$A$2:$A$10000 = $A6) * (Sheet1!$D$2:$D$10000 =
"EMAIL"))
> This works fine.
>
> The other field from Sheet1 that I want to check is a date field in the
> following format.
>
> 6/15/2005 9:13
>
>
> I want my report to be monthly, so I would like it check for $A6 and EMAIL
> above and in my example, month of June data only.
> Something like Month(data from Sheet1) = 6. I really don't want to use a
> helper column if not necessary.
>
> =SUMPRODUCT((Sheet1!$A$2:$A$10000 = $A6) * (Sheet1!$D$2:$D$10000 =
"EMAIL")
> * (Sheet1$C$2:$C$1000 = MONTH( not sure after this)
>
> Thanks
>
>
>

Hi Bob,

I tried your formula and it is giving me an error (that was the message two)



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--(Sheet1!$A$2:$A$10000 = $A6),--(Sheet1!$D$2:$D$10000 =
> "EMAIL"),--(TEXT(Sheet1$C$2:$C$1000,"mmm")="Jun"))
>
>
> --
> HTH
>
> Bob Phillips
>
> "syrac" <[email protected]> wrote in message
> news:%[email protected]...
> > This what I am looking to do. My data source is a SQL database.
> >
> > I have a query with three fields. I want to get counts on three
criteria,
> I
> > have the two working find, now need the date.
> > =SUMPRODUCT((Sheet1!$A$2:$A$10000 = $A6) * (Sheet1!$D$2:$D$10000 =
> "EMAIL"))
> > This works fine.
> >
> > The other field from Sheet1 that I want to check is a date field in the
> > following format.
> >
> > 6/15/2005 9:13
> >
> >
> > I want my report to be monthly, so I would like it check for $A6 and
EMAIL
> > above and in my example, month of June data only.
> > Something like Month(data from Sheet1) = 6. I really don't want to use
a
> > helper column if not necessary.
> >
> > =SUMPRODUCT((Sheet1!$A$2:$A$10000 = $A6) * (Sheet1!$D$2:$D$10000 =
> "EMAIL")
> > * (Sheet1$C$2:$C$1000 = MONTH( not sure after this)
> >
> > Thanks
> >
> >
> >
>
>

Change $C$1000 to $C$10000

all arrays must be the same size.

In article <[email protected]>,
"syrac" <[email protected]> wrote:

> Hi Bob,
>
> I tried your formula and it is giving me an error (that was the message two)
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > =SUMPRODUCT(--(Sheet1!$A$2:$A$10000 = $A6),--(Sheet1!$D$2:$D$10000 =
> > "EMAIL"),--(TEXT(Sheet1$C$2:$C$1000,"mmm")="Jun"))

Fixed that already, still getting the formula error


"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> Change $C$1000 to $C$10000
>
> all arrays must be the same size.
>
> In article <[email protected]>,
> "syrac" <[email protected]> wrote:
>
> > Hi Bob,
> >
> > I tried your formula and it is giving me an error (that was the message
two)
> >
> >
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > =SUMPRODUCT(--(Sheet1!$A$2:$A$10000 = $A6),--(Sheet1!$D$2:$D$10000 =
> > > "EMAIL"),--(TEXT(Sheet1$C$2:$C$1000,"mmm")="Jun"))

In article <u0r#[email protected]>,
"syrac" <[email protected]> wrote:

> Fixed that already, still getting the formula error

Would have been helpful to state that...

Did you also fix the


Sheet1$C$2

by changing it to

Sheet1!$C$2

??

If so, that formula works for me.

syrac wrote:
> This what I am looking to do. My data source is a SQL database.
>
> I have a query with three fields. I want to get counts on three criteria, I
> have the two working find, now need the date.
> =SUMPRODUCT((Sheet1!$A$2:$A$10000 = $A6) * (Sheet1!$D$2:$D$10000 = "EMAIL"))
> This works fine.
>
> The other field from Sheet1 that I want to check is a date field in the
> following format.
>
> 6/15/2005 9:13
>
>
> I want my report to be monthly, so I would like it check for $A6 and EMAIL
> above and in my example, month of June data only.
> Something like Month(data from Sheet1) = 6. I really don't want to use a
> helper column if not necessary.
>
> =SUMPRODUCT((Sheet1!$A$2:$A$10000 = $A6) * (Sheet1!$D$2:$D$10000 = "EMAIL")
> * (Sheet1$C$2:$C$1000 = MONTH( not sure after this)
>
> Thanks
>
>
>

=SUMPRODUCT((Sheet1!$A$2:$A$10000=$A6)+0,(Sheet1!$D$2:$D$10000="EMAIL")+0,(INT(Sheet1!$C$2:$C$10000-DAY(Sheet1!$C$2:$C$10000)+1)=$B6)+0)

where B6 houses a month/year of interest, set up as a first day date,
e.g., 6/1/2005 for June 2005.
--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

syrac wrote:
> This what I am looking to do. My data source is a SQL database.
>
> I have a query with three fields. I want to get counts on three criteria, I
> have the two working find, now need the date.

Didn't you say you were using SQL?

SELECT SUM(some_column) AS return_vale FROM MyTable WHERE other_colmn =
'EMAIL' AND another_column = <<value from A6 here>>

SELECT SUM(some_column) AS return_vale FROM MyTable WHERE other_colmn =
'EMAIL' AND MONTH(date_colmn) = MONTH(CURRENT_TIMESTAMP)

You could create a view on the database server to group the data. You
could create a proc on the server to pass the cell value. You could
create a join on the server between your table and your worksheet. You
could write queries in Excel using just the base tables in the
database. Make the database do the work so you don't have to.