XL2000 - Sumproduct Question

[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.

 

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

 

[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

 

[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

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.