Sumproduct from another worksheet

[Guest]

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"),COUNTIF(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

 

[Bob Phillips]

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)

 

[Guest]

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

CaroleO

 

[T. Valko]

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))