Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT that bring Text
=SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3),GDOS!$B$3:$B$10000)
This formula is trying to work as a VLOOKUP for text "1C" Right now the result of this formula is 0, does anyone know why and how can I correct this? Thanks |
#2
|
|||
|
|||
Does the target range have spaces at the front or end?
-- HTH Bob Phillips "MESTRELLA29" wrote in message ... =SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3),GDOS!$B$3:$B$10000) This formula is trying to work as a VLOOKUP for text "1C" Right now the result of this formula is 0, does anyone know why and how can I correct this? Thanks |
#3
|
|||
|
|||
is the c3 reference in the first cells in the current page or the GDOS page?
are the B column data actually values or could they be numbers as text. a quick check is to run each segment separately =sum(GDOS!$B$3:$B$10000) =SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3)) or =countif(GDOS!$C$3:$C$10000,$C3) "MESTRELLA29" wrote: =SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3),GDOS!$B$3:$B$10000) This formula is trying to work as a VLOOKUP for text "1C" Right now the result of this formula is 0, does anyone know why and how can I correct this? Thanks |
#4
|
|||
|
|||
yes C3 is in the corrent page sheet1 & is a Part no that can be found in GDOS
next to the 1C that is the value I need to bring to cells B in sheet1 It is Text "1B, 1C, 1S, 2B,2C, 2S...) "bj" wrote: is the c3 reference in the first cells in the current page or the GDOS page? are the B column data actually values or could they be numbers as text. a quick check is to run each segment separately =sum(GDOS!$B$3:$B$10000) =SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3)) or =countif(GDOS!$C$3:$C$10000,$C3) "MESTRELLA29" wrote: =SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3),GDOS!$B$3:$B$10000) This formula is trying to work as a VLOOKUP for text "1C" Right now the result of this formula is 0, does anyone know why and how can I correct this? Thanks |
#5
|
|||
|
|||
how did the other equations I recommended you try come out? I they didn't
come out ok try to find a cell in sheet GDOS that appears to be equal to what you have in your sheet1 C3 try =(c3=[select the cell in GDOS]) if the answer is TRUE and the reference to GDOS looks the same as what you have been using, I am totally confused. the only other thing I could think of is Whether the sum is just coincidentally 0. (I spent two days onetime tracking down a problem that wasn't there because the sum was truely 0) how did the other equaitons I recommended you try come out? "MESTRELLA29" wrote: yes C3 is in the corrent page sheet1 & is a Part no that can be found in GDOS next to the 1C that is the value I need to bring to cells B in sheet1 It is Text "1B, 1C, 1S, 2B,2C, 2S...) "bj" wrote: is the c3 reference in the first cells in the current page or the GDOS page? are the B column data actually values or could they be numbers as text. a quick check is to run each segment separately =sum(GDOS!$B$3:$B$10000) =SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3)) or =countif(GDOS!$C$3:$C$10000,$C3) "MESTRELLA29" wrote: =SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3),GDOS!$B$3:$B$10000) This formula is trying to work as a VLOOKUP for text "1C" Right now the result of this formula is 0, does anyone know why and how can I correct this? Thanks |
#6
|
|||
|
|||
MESTRELLA29 wrote:
=SUMPRODUCT(--(GDOS!$C$3:$C$10000=$C3),GDOS!$B$3:$B$10000) This formula is trying to work as a VLOOKUP for text "1C" Right now the result of this formula is 0, does anyone know why and how can I correct this? Thanks Two possibilities... (1) Cells in C might have extraneous (non-printable) chars around the entries (which you can remove with the TrimAll macro whose code you can track down with Google). (2) Numbers in B are not true numbers. To force them into true numbers: Copy an unused, empty cell. Select the range in B. Run Edit|Paste Special with the Add option checked. Finally, you have a single condition, that is, the range in C on GDOS must be equal to C3. In such cases, the right thing to do is to invoke a formula with SumIf... =SUMIF(GDOS!$C$3:$C$10000,$C3,GDOS!$B$3:$B$10000) which is an efficient (i.e., fast). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Bring all text from "Text" worksheet | Excel Discussion (Misc queries) | |||
using sumproduct in a range of text fields? | Excel Worksheet Functions |