special case of sumif

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 here:
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 here:
> 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
>