SUMIF question (although, I think I'm using the wrong function)
 

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the example
above, for 174327, it would equal 19. (13+6).

SUMIF question (although, I think I'm using the wrong function)
 

=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))
--
Gary''s Student - gsnu200909


"Ashley" wrote:

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the example
above, for 174327, it would equal 19. (13+6).

SUMIF question (although, I think I'm using the wrong function)
 

You can do this with the SUMPRODUCT function. Something like:

=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

The double negative (--) is in there to change the text output from the LEFT
function back into a number value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ashley" wrote:

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the example
above, for 174327, it would equal 19. (13+6).

SUMIF question (although, I think I'm using the wrong function
 

"Luke M" wrote:
=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

You seem to have several typos.


The double negative (--) is in there to change the text output
from the LEFT function back into a number value.

If that was your intent, you need the double negation to be adjacent to the
LEFT function, thus:

=SUMPRODUCT((--LEFT(A2:A100)=1743)*(B2:B100))

The also remedies your syntax error.

But your formula still does not make sense since the default length for LEFT
is 1 character. Perhaps you meant:

=SUMPRODUCT((--LEFT(A2:A100,4)=1743)*(B2:B100))

But why not write, more simply:

=SUMPRODUCT((LEFT(A2:A100,4)="1743")*(B2:B100))

with or without the parentheses around B2:B100.

Still, I would prefer:

=SUMPRODUCT(--(LEFT(A2:A100,4)="1743"), B2:B100)

since that avoids #VALUE errors if some of B2:B100 are non-numeric


----- original message -----

"Luke M" wrote:
You can do this with the SUMPRODUCT function. Something like:

=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

The double negative (--) is in there to change the text output from the LEFT
function back into a number value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ashley" wrote:

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the example
above, for 174327, it would equal 19. (13+6).

SUMIF question (although, I think I'm using the wrong function)
 

Sorry to be pedantic but the double negation converts Boolean (FALSE/TRUE)
to numbers (0/1), it is not converting text. See
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Luke M" wrote in message
...
You can do this with the SUMPRODUCT function. Something like:

=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

The double negative (--) is in there to change the text output from the
LEFT
function back into a number value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ashley" wrote:

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the
example
above, for 174327, it would equal 19. (13+6).

SUMIF question (although, I think I'm using the wrong function
 

Thanks Joe. Guess this is what happens the closer I get to the weekend...
Have a good one.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Joe User" wrote:

"Luke M" wrote:
=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

You seem to have several typos.


The double negative (--) is in there to change the text output
from the LEFT function back into a number value.

If that was your intent, you need the double negation to be adjacent to the
LEFT function, thus:

=SUMPRODUCT((--LEFT(A2:A100)=1743)*(B2:B100))

The also remedies your syntax error.

But your formula still does not make sense since the default length for LEFT
is 1 character. Perhaps you meant:

=SUMPRODUCT((--LEFT(A2:A100,4)=1743)*(B2:B100))

But why not write, more simply:

=SUMPRODUCT((LEFT(A2:A100,4)="1743")*(B2:B100))

with or without the parentheses around B2:B100.

Still, I would prefer:

=SUMPRODUCT(--(LEFT(A2:A100,4)="1743"), B2:B100)

since that avoids #VALUE errors if some of B2:B100 are non-numeric


----- original message -----

"Luke M" wrote:
You can do this with the SUMPRODUCT function. Something like:

=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

The double negative (--) is in there to change the text output from the LEFT
function back into a number value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ashley" wrote:

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the example
above, for 174327, it would equal 19. (13+6).

SUMIF question (although, I think I'm using the wrong function
 

"Gary''s Student" wrote:
=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))

The double negation before B1:B5 might be useful if you assume B1:B5
contains numeric strings (text). But in that case I would prefer the simpler
form:

=SUMPRODUCT((LEFT(A1:A5,4)="1743")*(B1:B5))

with or without the parentheses around B1:B5.

Otherwise, the double negation before B1:B5 seems superfluous, as are the
parentheses. More to the point, --B1:B5 defeats the purpose of writing
separate SUMPRODUCT arguments.

If B1:B5 is expected to contain numbers, I would prefer:

=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"), B1:B5)

since that avoids a #VALUE if some of B1:B5 are purposely non-numeric and to
be ignored.


----- original message -----

"Gary''s Student" wrote:

=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))
--
Gary''s Student - gsnu200909


"Ashley" wrote:

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the example
above, for 174327, it would equal 19. (13+6).

SUMIF question (although, I think I'm using the wrong function
 

"Luke M" wrote:
Thanks Joe. Guess this is what happens the closer I get to the weekend.

Been there, done that! :-)


Have a good one.

I certainly will, since I will finally return home to a less stressful
environment.


----- original mail -----

"Luke M" wrote in message
...
Thanks Joe. Guess this is what happens the closer I get to the weekend...
Have a good one.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Joe User" wrote:

"Luke M" wrote:
=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

You seem to have several typos.


The double negative (--) is in there to change the text output
from the LEFT function back into a number value.

If that was your intent, you need the double negation to be adjacent to
the
LEFT function, thus:

=SUMPRODUCT((--LEFT(A2:A100)=1743)*(B2:B100))

The also remedies your syntax error.

But your formula still does not make sense since the default length for
LEFT
is 1 character. Perhaps you meant:

=SUMPRODUCT((--LEFT(A2:A100,4)=1743)*(B2:B100))

But why not write, more simply:

=SUMPRODUCT((LEFT(A2:A100,4)="1743")*(B2:B100))

with or without the parentheses around B2:B100.

Still, I would prefer:

=SUMPRODUCT(--(LEFT(A2:A100,4)="1743"), B2:B100)

since that avoids #VALUE errors if some of B2:B100 are non-numeric


----- original message -----

"Luke M" wrote:
You can do this with the SUMPRODUCT function. Something like:

=SUMPRODUCT((--(LEFT(A2:A100)=1743)*(B2:B100))

The double negative (--) is in there to change the text output from the
LEFT
function back into a number value.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ashley" wrote:

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from
the 6
digit number, then adds the totals from their call amounts. IN the
example
above, for 174327, it would equal 19. (13+6).

SUMIF question (although, I think I'm using the wrong function
 

Your comments are very clear and informative.

Thanks for your input!
--
Gary''s Student - gsnu200909


"Joe User" wrote:

"Gary''s Student" wrote:
=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))

The double negation before B1:B5 might be useful if you assume B1:B5
contains numeric strings (text). But in that case I would prefer the simpler
form:

=SUMPRODUCT((LEFT(A1:A5,4)="1743")*(B1:B5))

with or without the parentheses around B1:B5.

Otherwise, the double negation before B1:B5 seems superfluous, as are the
parentheses. More to the point, --B1:B5 defeats the purpose of writing
separate SUMPRODUCT arguments.

If B1:B5 is expected to contain numbers, I would prefer:

=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"), B1:B5)

since that avoids a #VALUE if some of B1:B5 are purposely non-numeric and to
be ignored.


----- original message -----

"Gary''s Student" wrote:

=SUMPRODUCT(--(LEFT(A1:A5,4)="1743"),--(B1:B5))
--
Gary''s Student - gsnu200909


"Ashley" wrote:

I have a 2 column log that tracks incoming call amounts from agents
identified as 6 digit numbers CCOOAA - County, Office, Agent#:

171917 4
174327 6
171629 9
174327 13
172832 5

I want to add another table that takes ONLY the first 4 digits from the 6
digit number, then adds the totals from their call amounts. IN the example
above, for 174327, it would equal 19. (13+6).