| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct cell reference vs embedded "values"
I have experimented with all sorts of different combinations and there is
clearly some syntax issue that I am running afowl of or something I do not understand about the way this works. In trying to compare and count the outcome using two column ranges, looking for one name any number of times in the first column and only including that in the count if the corresponding column position is a blank cell, this formula works: =SUMPRODUCT((F2:F200=W2)*(Q2:Q200=AC10)) ....where cell W2 is "7566" and cell AC10 is blank. But when I try to embed the cell contents directly into the formula i.e. =SUMPRODUCT((F2:F200="7566")*(Q2:Q200="")) ....it does not work. Insight please?! Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct cell reference vs embedded "values"
Remove quotes around the number 7566
"BobMcBarker" wrote: I have experimented with all sorts of different combinations and there is clearly some syntax issue that I am running afowl of or something I do not understand about the way this works. In trying to compare and count the outcome using two column ranges, looking for one name any number of times in the first column and only including that in the count if the corresponding column position is a blank cell, this formula works: =SUMPRODUCT((F2:F200=W2)*(Q2:Q200=AC10)) ...where cell W2 is "7566" and cell AC10 is blank. But when I try to embed the cell contents directly into the formula i.e. =SUMPRODUCT((F2:F200="7566")*(Q2:Q200="")) ...it does not work. Insight please?! Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct cell reference vs embedded "values"
Many thanks...easy, but I completely spaced it!
"Teethless mama" wrote: Remove quotes around the number 7566 "BobMcBarker" wrote: I have experimented with all sorts of different combinations and there is clearly some syntax issue that I am running afowl of or something I do not understand about the way this works. In trying to compare and count the outcome using two column ranges, looking for one name any number of times in the first column and only including that in the count if the corresponding column position is a blank cell, this formula works: =SUMPRODUCT((F2:F200=W2)*(Q2:Q200=AC10)) ...where cell W2 is "7566" and cell AC10 is blank. But when I try to embed the cell contents directly into the formula i.e. =SUMPRODUCT((F2:F200="7566")*(Q2:Q200="")) ...it does not work. Insight please?! Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct cell reference vs embedded "values"
You're Welcome!
"BobMcBarker" wrote: Many thanks...easy, but I completely spaced it! "Teethless mama" wrote: Remove quotes around the number 7566 "BobMcBarker" wrote: I have experimented with all sorts of different combinations and there is clearly some syntax issue that I am running afowl of or something I do not understand about the way this works. In trying to compare and count the outcome using two column ranges, looking for one name any number of times in the first column and only including that in the count if the corresponding column position is a blank cell, this formula works: =SUMPRODUCT((F2:F200=W2)*(Q2:Q200=AC10)) ...where cell W2 is "7566" and cell AC10 is blank. But when I try to embed the cell contents directly into the formula i.e. =SUMPRODUCT((F2:F200="7566")*(Q2:Q200="")) ...it does not work. Insight please?! Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| combining values and text to make a reference for "named range" | Excel Worksheet Functions | |||
| cell reference "values only" | Excel Worksheet Functions | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Changing "returned" values from "0" to "blank" | Excel Worksheet Functions | |||
Linear Mode
