Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct against worksheet vs named range- any speed difference? | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
plot graph from multiple worksheet as embedded chart object on every worksheet | Charts and Charting in Excel | |||
Sumproduct from different worksheet | Excel Worksheet Functions | |||
Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions |