Sumproduct from another worksheet
 

Excel 2000

I have an INPUT_FORM tab and a REPORT 2007 tab in the same spreadsheet.

I have the following formula in REPORT 2007 to select out only the "YES"
values if there is a match to the month:

=SUMPRODUCT(--INPUT_FORM!$B$2:$B$1000,"AUGUST"),--(INPUT_FORM!$J2:$J$1000,"YES"))

I get a message saying there is something wrong with the formula, but can't
figure out what it is. I also tried with COUNTIF, and putting single quotes
around 'INPUT_FORM' - to no avail. Column J contains "YES", "NO", "Remind"

When I use
=MIN(COUNTIF(INPUT_FORM!$B$2:$B$1000,"AUGUST"),COU NTIF(INPUT_FORM!$J2:$J$1000,"YES"))
I get the total count of all Yes, no, remind which is 5, when the correct
answer is 4 YES cells.

Any ideas?

CaroleO

Sumproduct from another worksheet
 

You are missing a bracket, and no =

=SUMPRODUCT(--(INPUT_FORM!$B$2:$B$1000="AUGUST"),--(INPUT_FORM!$J2:$J$1000="YES"))



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Carole O" wrote in message
...
Excel 2000

I have an INPUT_FORM tab and a REPORT 2007 tab in the same spreadsheet.

I have the following formula in REPORT 2007 to select out only the "YES"
values if there is a match to the month:

=SUMPRODUCT(--INPUT_FORM!$B$2:$B$1000,"AUGUST"),--(INPUT_FORM!$J2:$J$1000,"YES"))

I get a message saying there is something wrong with the formula, but
can't
figure out what it is. I also tried with COUNTIF, and putting single
quotes
around 'INPUT_FORM' - to no avail. Column J contains "YES", "NO",
"Remind"

When I use
=MIN(COUNTIF(INPUT_FORM!$B$2:$B$1000,"AUGUST"),COU NTIF(INPUT_FORM!$J2:$J$1000,"YES"))
I get the total count of all Yes, no, remind which is 5, when the correct
answer is 4 YES cells.

Any ideas?

CaroleO

Sumproduct from another worksheet
 

Thanks, Bob - that did the trick! I appreciate your quick response.

CaroleO

"Bob Phillips" wrote:

You are missing a bracket, and no =

=SUMPRODUCT(--(INPUT_FORM!$B$2:$B$1000="AUGUST"),--(INPUT_FORM!$J2:$J$1000="YES"))



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Carole O" wrote in message
...
Excel 2000

I have an INPUT_FORM tab and a REPORT 2007 tab in the same spreadsheet.

I have the following formula in REPORT 2007 to select out only the "YES"
values if there is a match to the month:

=SUMPRODUCT(--INPUT_FORM!$B$2:$B$1000,"AUGUST"),--(INPUT_FORM!$J2:$J$1000,"YES"))

I get a message saying there is something wrong with the formula, but
can't
figure out what it is. I also tried with COUNTIF, and putting single
quotes
around 'INPUT_FORM' - to no avail. Column J contains "YES", "NO",
"Remind"

When I use
=MIN(COUNTIF(INPUT_FORM!$B$2:$B$1000,"AUGUST"),COU NTIF(INPUT_FORM!$J2:$J$1000,"YES"))
I get the total count of all Yes, no, remind which is 5, when the correct
answer is 4 YES cells.

Any ideas?

CaroleO

Sumproduct from another worksheet
 

Try it like this:

=SUMPRODUCT(--(Input_Form!$B$2:$B$1000="August"),--(Input_Form!$J2:$J$1000="Yes"))

Better to use cells to hold the criteria:

A1 = August
A2 = Yes

=SUMPRODUCT(--(Input_Form!$B$2:$B$1000=A1),--(Input_Form!$J2:$J$1000=A2))

--
Biff
Microsoft Excel MVP


"Carole O" wrote in message
...
Excel 2000

I have an INPUT_FORM tab and a REPORT 2007 tab in the same spreadsheet.

I have the following formula in REPORT 2007 to select out only the "YES"
values if there is a match to the month:

=SUMPRODUCT(--INPUT_FORM!$B$2:$B$1000,"AUGUST"),--(INPUT_FORM!$J2:$J$1000,"YES"))

I get a message saying there is something wrong with the formula, but
can't
figure out what it is. I also tried with COUNTIF, and putting single
quotes
around 'INPUT_FORM' - to no avail. Column J contains "YES", "NO",
"Remind"

When I use
=MIN(COUNTIF(INPUT_FORM!$B$2:$B$1000,"AUGUST"),COU NTIF(INPUT_FORM!$J2:$J$1000,"YES"))
I get the total count of all Yes, no, remind which is 5, when the correct
answer is 4 YES cells.

Any ideas?

CaroleO