sumproduct & indirect

i am trying to use sumproduct to both:

1. Count a quanity based on search criteria
2. Sum a column based on search criteria

My formula looks like this so far
=SUMPRODUCT(INDIRECT(--("CLIENT!$I$2:$I"&$N$1="SUNBELT")),(INDIRECT(--("CLIENT!$J$2:$J"&$N$1="YES"))),(INDIRECT(--("CLIENT!$K$2:$K"&$N$1=NO))))

I was trying to get this formula to locate data on "client!" and count the
occurances
of the search criteria happening at the same time, while indirectly
referencing a number on my "calculations" sheet ( N1 ) which has the final
row # to search through

The current formula returns a #ref remark

Is it possible to combine these funcitons & can you then change formula to
sum a column (i.e. column Q) based on the same criteria.

=SUMPRODUCT(--(INDIRECT("CLIENT!$I$2:$I"&$N$1)="SUNBELT"),--(INDIRECT("CLIEN
T!$J$2:$J"&$N$1)="YES"),--(INDIRECT("CLIENT!$K$2:$K"&$N$1)="NO"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"floridasurfn" <[email protected]> wrote in message
news:[email protected]...
> i am trying to use sumproduct to both:
>
> 1. Count a quanity based on search criteria
> 2. Sum a column based on search criteria
>
> My formula looks like this so far
>
=SUMPRODUCT(INDIRECT(--("CLIENT!$I$2:$I"&$N$1="SUNBELT")),(INDIRECT(--("CLIE
NT!$J$2:$J"&$N$1="YES"))),(INDIRECT(--("CLIENT!$K$2:$K"&$N$1=NO))))
>
> I was trying to get this formula to locate data on "client!" and count the
> occurances
> of the search criteria happening at the same time, while indirectly
> referencing a number on my "calculations" sheet ( N1 ) which has the final
> row # to search through
>
> The current formula returns a #ref remark
>
> Is it possible to combine these funcitons & can you then change formula to
> sum a column (i.e. column Q) based on the same criteria.

Use it like this

SUMPRODUCT(--(INDIRECT("'CLIENT'!$I$2:$I"&$N$1)="SUNBELT"),--and so on

adapt this to the other ranges

I assume you forgot to add the quotations to "NO" in your example


--

Regards,

Peo Sjoblom


"floridasurfn" <[email protected]> wrote in message
news:[email protected]...
> i am trying to use sumproduct to both:
>
> 1. Count a quanity based on search criteria
> 2. Sum a column based on search criteria
>
> My formula looks like this so far
>
=SUMPRODUCT(INDIRECT(--("CLIENT!$I$2:$I"&$N$1="SUNBELT")),(INDIRECT(--("CLIE
NT!$J$2:$J"&$N$1="YES"))),(INDIRECT(--("CLIENT!$K$2:$K"&$N$1=NO))))
>
> I was trying to get this formula to locate data on "client!" and count the
> occurances
> of the search criteria happening at the same time, while indirectly
> referencing a number on my "calculations" sheet ( N1 ) which has the final
> row # to search through
>
> The current formula returns a #ref remark
>
> Is it possible to combine these funcitons & can you then change formula to
> sum a column (i.e. column Q) based on the same criteria.

thanks guys both of these helped me out ... sorry i took so long to reply

"floridasurfn" wrote:

> i am trying to use sumproduct to both:
>
> 1. Count a quanity based on search criteria
> 2. Sum a column based on search criteria
>
> My formula looks like this so far
> =SUMPRODUCT(INDIRECT(--("CLIENT!$I$2:$I"&$N$1="SUNBELT")),(INDIRECT(--("CLIENT!$J$2:$J"&$N$1="YES"))),(INDIRECT(--("CLIENT!$K$2:$K"&$N$1=NO))))
>
> I was trying to get this formula to locate data on "client!" and count the
> occurances
> of the search criteria happening at the same time, while indirectly
> referencing a number on my "calculations" sheet ( N1 ) which has the final
> row # to search through
>
> The current formula returns a #ref remark
>
> Is it possible to combine these funcitons & can you then change formula to
> sum a column (i.e. column Q) based on the same criteria.