SUMPRODUCT that bring Text

M

MESTRELLA29

=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
 
B

bj

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

MESTRELLA29

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

bj

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?
 
A

Aladin Akyurek

MESTRELLA29 said:
=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
Click to expand...

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