Hi all,
I am trying to do a sum but based on a substring instead of the entire
contents of a cell. example, A1:A4={"monday, tuesday", "wednesday","
thursday","friday"} and B1:B4={2,4,1,5}. the values in column A may have 2
days in a single string and I want to add based on any one. example
sumif(A1:A4,"Monday",B1:B4) should give 2 and sumif(A1:A4,"Tuesday",B1:B4)
should also give 2.

any help on this will be greatly appreciated...thanx a lot.

Like:

=SUMIF(A1:A4,"*Monday*",B1:B4)

????


delali wrote:

Hi all,
I am trying to do a sum but based on a substring instead of the entire
contents of a cell. example, A1:A4={"monday, tuesday", "wednesday","
thursday","friday"} and B1:B4={2,4,1,5}. the values in column A may have 2
days in a single string and I want to add based on any one. example
sumif(A1:A4,"Monday",B1:B4) should give 2 and sumif(A1:A4,"Tuesday",B1:B4)
should also give 2.

any help on this will be greatly appreciated...thanx a lot.

--

Dave Peterson

thanx Dave, that works great.

is it possible to extend this to a sumproduct formula?

example, =SUMPRODUCT((A4:A15=("January"))*(B4:B15="Monday") ) should also
count cells in B4:B15 where the cell contains both Monday and tuesday.

something like =SUMPRODUCT((A4:A15=("January"))*(B4:B15="*Monday* ")) but
this doesn't work because the two ** are considered as part of the string
being searched for. I need some kind of escape character.

thanx in advance...

"Dave Peterson" wrote:

Like:

=SUMIF(A1:A4,"*Monday*",B1:B4)

????


delali wrote:

Hi all,
I am trying to do a sum but based on a substring instead of the entire
contents of a cell. example, A1:A4={"monday, tuesday", "wednesday","
thursday","friday"} and B1:B4={2,4,1,5}. the values in column A may have 2
days in a single string and I want to add based on any one. example
sumif(A1:A4,"Monday",B1:B4) should give 2 and sumif(A1:A4,"Tuesday",B1:B4)
should also give 2.

any help on this will be greatly appreciated...thanx a lot.

--

Dave Peterson

=sumproduct(--(a4:a15="january"),--(isnumber(search("monday",b4:b15))))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

delali wrote:

thanx Dave, that works great.

is it possible to extend this to a sumproduct formula?

example, =SUMPRODUCT((A4:A15=("January"))*(B4:B15="Monday") ) should also
count cells in B4:B15 where the cell contains both Monday and tuesday.

something like =SUMPRODUCT((A4:A15=("January"))*(B4:B15="*Monday* ")) but
this doesn't work because the two ** are considered as part of the string
being searched for. I need some kind of escape character.

thanx in advance...

"Dave Peterson" wrote:

Like:

=SUMIF(A1:A4,"*Monday*",B1:B4)

????


delali wrote:

Hi all,
I am trying to do a sum but based on a substring instead of the entire
contents of a cell. example, A1:A4={"monday, tuesday", "wednesday","
thursday","friday"} and B1:B4={2,4,1,5}. the values in column A may have 2
days in a single string and I want to add based on any one. example
sumif(A1:A4,"Monday",B1:B4) should give 2 and sumif(A1:A4,"Tuesday",B1:B4)
should also give 2.

any help on this will be greatly appreciated...thanx a lot.

--

Dave Peterson


--

Dave Peterson

Thanx a bunch Dave ..... u're the man!!!

"Dave Peterson" wrote:

=sumproduct(--(a4:a15="january"),--(isnumber(search("monday",b4:b15))))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

delali wrote:

thanx Dave, that works great.

is it possible to extend this to a sumproduct formula?

example, =SUMPRODUCT((A4:A15=("January"))*(B4:B15="Monday") ) should also
count cells in B4:B15 where the cell contains both Monday and tuesday.

something like =SUMPRODUCT((A4:A15=("January"))*(B4:B15="*Monday* ")) but
this doesn't work because the two ** are considered as part of the string
being searched for. I need some kind of escape character.

thanx in advance...

"Dave Peterson" wrote:

Like:

=SUMIF(A1:A4,"*Monday*",B1:B4)

????


delali wrote:

Hi all,
I am trying to do a sum but based on a substring instead of the entire
contents of a cell. example, A1:A4={"monday, tuesday", "wednesday","
thursday","friday"} and B1:B4={2,4,1,5}. the values in column A may have 2
days in a single string and I want to add based on any one. example
sumif(A1:A4,"Monday",B1:B4) should give 2 and sumif(A1:A4,"Tuesday",B1:B4)
should also give 2.

any help on this will be greatly appreciated...thanx a lot.

--

Dave Peterson


--

Dave Peterson