XL2000 - Sumproduct Question

L

LPS

I have 12 spreadsheets. 11 are client lists by caseworker (11 case workers,
11 client lists). The other is a summary sheet. In the summary sheet, I am
trying to summarise, by caseworker, the number if clients which match
specfiic criteria. For example, in the caseworker detail sheet (CAWL -
Cloutier, Teresa .xls]Case Activity) there is a column (column I) identifying
the status (Active, Outcome or Dormant). Then there is a column (Column F)
identifying the type of case (TW, PW, etc). Then there is the client name
column (Column B).

I am trying to use the SUMPRODUCTfunction to add up the number of clients
whose status is Active and whose type it TW, as follows:

=SUMPRODUCT('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$I$18:$I$1000="Active")*('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$F$18:$F$1000="tw")*('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$B$18:$B$1000<>"")

I am entering it as an array formula but get 0 (zero) as the result. There
are at least 2 entries which should match this set of criteria. Can anyone
tell me what I am doing wrong? I am fairly new to the SUMPRODUCT function.

Thank you for any and all help.
 
L

LPS

I think I found my own answer... YAY... I forgot to use the unary operators
to convert the alpha data to numeric data. Does that sound correct?
 
S

Shane Devenshire

Hi,

Start by change to

=SUMPRODUCT(--('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$I$18:$I$1000="Active"),--('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$F$18:$F$1000="tw"),--('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$B$18:$B$1000<>""))

Although I think you were missing a parenthesis in one place in your
original formula.

If this helps, please click the Yes button

Cheers,
Shane Devensire
 
D

Dave Peterson

Nope.

Multiplying the arguments will coerce the booleans to 1's and 0's.

=SUMPRODUCT(
('[CAWL - Cloutier, Teresa .xls]Case Activity'!$I$18:$I$1000="Active")
*('[CAWL - Cloutier, Teresa .xls]Case Activity'!$F$18:$F$1000="tw")
*('[CAWL - Cloutier, Teresa .xls]Case Activity'!$B$18:$B$1000<>""))

It was the missing ()'s.
I think I found my own answer... YAY... I forgot to use the unary operators
to convert the alpha data to numeric data. Does that sound correct?
--
LPS

LPS said:
I have 12 spreadsheets. 11 are client lists by caseworker (11 case workers,
11 client lists). The other is a summary sheet. In the summary sheet, I am
trying to summarise, by caseworker, the number if clients which match
specfiic criteria. For example, in the caseworker detail sheet (CAWL -
Cloutier, Teresa .xls]Case Activity) there is a column (column I) identifying
the status (Active, Outcome or Dormant). Then there is a column (Column F)
identifying the type of case (TW, PW, etc). Then there is the client name
column (Column B).

I am trying to use the SUMPRODUCTfunction to add up the number of clients
whose status is Active and whose type it TW, as follows:

=SUMPRODUCT('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$I$18:$I$1000="Active")*('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$F$18:$F$1000="tw")*('[CAWL - Cloutier, Teresa .xls]Case
Activity'!$B$18:$B$1000<>"")

I am entering it as an array formula but get 0 (zero) as the result. There
are at least 2 entries which should match this set of criteria. Can anyone
tell me what I am doing wrong? I am fairly new to the SUMPRODUCT function.

Thank you for any and all help.
 

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

Top