Using a SUMIF and Parsing to sum part of a cell

[Greg]

Hello all,

I'm thinking that I need some type of array formula, sumif, etc. for
this but I'll let the group decide.

I want to have a SUMIF type formula where it can parse the sum field
and add the value on the left or right of a comma. For example,
=SUMIF(A1:A10,"f/p",B1:B10) where the values in A4 and A6 are "f/p"
and the values in B4 and B6 are 1,2 and 2,4. I want to be able to add
just the 2 and the 4 (numbers to the right of the comma). Can this be
done somehow or is there a better way of doing it?

Thanks!

 

[joeu2004]

I want to have a SUMIF type formula where it can parse the sum field
and add the value on the left or right of a comma.  For example,
=SUMIF(A1:A10,"f/p",B1:B10) where the values in A4 and A6 are "f/p"
and the values in B4 and B6 are 1,2 and 2,4.  I want to be able to add
just the 2 and the 4 (numbers to the right of the comma).  Can this be
done somehow or is there a better way of doing it?

This cannot be done with SUMIF. However, this should do it....

If the number to the right of the comma is always a single digit:

=SUMPRODUCT(--(A1:A10="f/p"),--RIGHT(B1:B10,1))

More generally:

=SUMPRODUCT(--(A1:A10="f/p"),--MID(B1:B10,1+FIND(",",B1:B10),99))

The double negation (--) converts logic values (TRUE, FALSE) and text
numbers to bona fide numeric values (1 and 0 for logic values), which
SUMPRODUCT requires.

 

[Murray]

Greg

Simplest, if you don't mind using a third column, is to put the
following in C1 and copy down.

=VALUE(MID(B1,FIND(",",B1,1)+1,10))

Then change B1:B10 in your SUMIF to C1:C10

Regards

Murray

 

[Greg]

This cannot be done with SUMIF.  However, this should do it....

If the number to the right of the comma is always a single digit:

=SUMPRODUCT(--(A1:A10="f/p"),--RIGHT(B1:B10,1))

More generally:

=SUMPRODUCT(--(A1:A10="f/p"),--MID(B1:B10,1+FIND(",",B1:B10),99))

The double negation (--) converts logic values (TRUE, FALSE) and text
numbers to bona fide numeric values (1 and 0 for logic values), which
SUMPRODUCT requires.

@joeu2004 Thanks, it works great except for one problem. The cells in
B1:B10 that do not have any values give an #VALUE! error which
pollutes the rest of the result. I'm trying to think of a way around
it but drawing a blank.

 

[Greg]

@joeu2004 Thanks, it works great except for one problem. The cells in
B1:B10 that do not have any values give an #VALUE! error which
pollutes the rest of the result.  I'm trying to think of a way around
it but drawing a blank.

Nvm. I figured it out. =SUMPRODUCT(--(B9:AF9="f/p"),IF(ISERROR(--
RIGHT(B10:AF10,1)),0,--RIGHT(B10:AF10,1)))

This ignores the error once you array it.

Thanks again for the awesome formula!

 

[joeu2004]

[....]
@joeu2004 Thanks, it works great except for one problem. The cells
in B1:B10 that do not have any values give an #VALUE! error which
pollutes the rest of the result.  I'm trying to think of a way around
it but drawing a blank.

In that case, I would suggest the following array formula [*]:

=SUM(IF(A1:A10="f/p",
IF(ISNUMBER(MID(B1:B10,1+FIND(",",B1:B10),99)),
--MID(B1:B10,1+FIND(",",B1:B10),99))))

If you have XL2007 or later, use the following normal (non-array)
formula:

=SUMPRODUCT((A1:A10="f/p")
*IFERROR(--MID(B1:B10,1+FIND(",",B1:B10),99),0))

[*] Enter an array formula by pressing ctrl+shift+Enter instead of
just Enter. Excel displays array formulas surrounded with curly
braces in the Formula Bar, i.e. {=formula}. You cannot type the curly
braces yourself. If you make a mistake, select the cell, press F2 and
edit as needed, then press ctrl+shift+Enter.

 

[joeu2004]

Nvm. I figured it out.  =SUMPRODUCT(--(B9:AF9="f/p"),IF(ISERROR(--
RIGHT(B10:AF10,1)),0,--RIGHT(B10:AF10,1)))
This ignores the error once you array it.

Yes. I ass-u-me-d you used the FIND formula.

If you have XL2007 or later, use the following normal (non-array)
formula:
=SUMPRODUCT((A1:A10="f/p")
*IFERROR(--MID(B1:B10,1+FIND(",",B1:B10),99),0))

You are correct: that needs to be an array formula. My bad!

 

[Ron Rosenfeld]

Hello all,

I'm thinking that I need some type of array formula, sumif, etc. for
this but I'll let the group decide.

I want to have a SUMIF type formula where it can parse the sum field
and add the value on the left or right of a comma. For example,
=SUMIF(A1:A10,"f/p",B1:B10) where the values in A4 and A6 are "f/p"
and the values in B4 and B6 are 1,2 and 2,4. I want to be able to add
just the 2 and the 4 (numbers to the right of the comma). Can this be
done somehow or is there a better way of doing it?

Thanks!

If your comma represents the decimal point, then:

=SUMPRODUCT((A1:A10="f/p")*MOD(B1:B10,1))

or, depending on what you want exactly, maybe:

=SUMPRODUCT((A1:A10="f/p")*MOD(B1:B10,1)*10)