software

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={quot;monday, tuesdayquot;, quot;wednesdayquot;,quot;
thursdayquot;,quot;fridayquot;} 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,quot;Mondayquot;,B1:B4) should give 2 and sumif(A1:A4,quot;Tuesdayquot;,B1:B4)
should also give 2.

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

Like:

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

????delali wrote:
gt;
gt; Hi all,
gt; I am trying to do a sum but based on a substring instead of the entire
gt; contents of a cell. example, A1:A4={quot;monday, tuesdayquot;, quot;wednesdayquot;,quot;
gt; thursdayquot;,quot;fridayquot;} and B1:B4={2,4,1,5}. the values in column A may have 2
gt; days in a single string and I want to add based on any one. example
gt; sumif(A1:A4,quot;Mondayquot;,B1:B4) should give 2 and sumif(A1:A4,quot;Tuesdayquot;,B1:B4)
gt; should also give 2.
gt;
gt; 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=(quot;Januaryquot;))*(B4:B15=quot;Mondayquot;) ) should also
count cells in B4:B15 where the cell contains both Monday and tuesday.

something like =SUMPRODUCT((A4:A15=(quot;Januaryquot;))*(B4:B15=quot;*Monday* quot;)) 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...

quot;Dave Petersonquot; wrote:

gt; Like:
gt;
gt; =SUMIF(A1:A4,quot;*Monday*quot;,B1:B4)
gt;
gt; ????
gt;
gt;
gt; delali wrote:
gt; gt;
gt; gt; Hi all,
gt; gt; I am trying to do a sum but based on a substring instead of the entire
gt; gt; contents of a cell. example, A1:A4={quot;monday, tuesdayquot;, quot;wednesdayquot;,quot;
gt; gt; thursdayquot;,quot;fridayquot;} and B1:B4={2,4,1,5}. the values in column A may have 2
gt; gt; days in a single string and I want to add based on any one. example
gt; gt; sumif(A1:A4,quot;Mondayquot;,B1:B4) should give 2 and sumif(A1:A4,quot;Tuesdayquot;,B1:B4)
gt; gt; should also give 2.
gt; gt;
gt; gt; any help on this will be greatly appreciated...thanx a lot.
gt;
gt; --
gt;
gt; Dave Peterson
gt;

=sumproduct(--(a4:a15=quot;januaryquot;),--(isnumber(search(quot;mondayquot;,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
www.xldynamic.com/source/xld.SUMPRODUCT.html

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

delali wrote:
gt;
gt; thanx Dave, that works great.
gt;
gt; is it possible to extend this to a sumproduct formula?
gt;
gt; example, =SUMPRODUCT((A4:A15=(quot;Januaryquot;))*(B4:B15=quot;Mondayquot;) ) should also
gt; count cells in B4:B15 where the cell contains both Monday and tuesday.
gt;
gt; something like =SUMPRODUCT((A4:A15=(quot;Januaryquot;))*(B4:B15=quot;*Monday* quot;)) but
gt; this doesn't work because the two ** are considered as part of the string
gt; being searched for. I need some kind of escape character.
gt;
gt; thanx in advance...
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Like:
gt; gt;
gt; gt; =SUMIF(A1:A4,quot;*Monday*quot;,B1:B4)
gt; gt;
gt; gt; ????
gt; gt;
gt; gt;
gt; gt; delali wrote:
gt; gt; gt;
gt; gt; gt; Hi all,
gt; gt; gt; I am trying to do a sum but based on a substring instead of the entire
gt; gt; gt; contents of a cell. example, A1:A4={quot;monday, tuesdayquot;, quot;wednesdayquot;,quot;
gt; gt; gt; thursdayquot;,quot;fridayquot;} and B1:B4={2,4,1,5}. the values in column A may have 2
gt; gt; gt; days in a single string and I want to add based on any one. example
gt; gt; gt; sumif(A1:A4,quot;Mondayquot;,B1:B4) should give 2 and sumif(A1:A4,quot;Tuesdayquot;,B1:B4)
gt; gt; gt; should also give 2.
gt; gt; gt;
gt; gt; gt; any help on this will be greatly appreciated...thanx a lot.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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

quot;Dave Petersonquot; wrote:

gt; =sumproduct(--(a4:a15=quot;januaryquot;),--(isnumber(search(quot;mondayquot;,b4:b15))))
gt;
gt; Adjust the ranges to match--but you can't use whole columns.
gt;
gt; =sumproduct() likes to work with numbers. The -- stuff changes trues and falses
gt; to 1's and 0's.
gt;
gt; Bob Phillips explains =sumproduct() in much more detail he
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt;
gt; And J.E. McGimpsey has some notes at:
gt; mcgimpsey.com/excel/formulae/doubleneg.html
gt;
gt; delali wrote:
gt; gt;
gt; gt; thanx Dave, that works great.
gt; gt;
gt; gt; is it possible to extend this to a sumproduct formula?
gt; gt;
gt; gt; example, =SUMPRODUCT((A4:A15=(quot;Januaryquot;))*(B4:B15=quot;Mondayquot;) ) should also
gt; gt; count cells in B4:B15 where the cell contains both Monday and tuesday.
gt; gt;
gt; gt; something like =SUMPRODUCT((A4:A15=(quot;Januaryquot;))*(B4:B15=quot;*Monday* quot;)) but
gt; gt; this doesn't work because the two ** are considered as part of the string
gt; gt; being searched for. I need some kind of escape character.
gt; gt;
gt; gt; thanx in advance...
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; Like:
gt; gt; gt;
gt; gt; gt; =SUMIF(A1:A4,quot;*Monday*quot;,B1:B4)
gt; gt; gt;
gt; gt; gt; ????
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; delali wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Hi all,
gt; gt; gt; gt; I am trying to do a sum but based on a substring instead of the entire
gt; gt; gt; gt; contents of a cell. example, A1:A4={quot;monday, tuesdayquot;, quot;wednesdayquot;,quot;
gt; gt; gt; gt; thursdayquot;,quot;fridayquot;} and B1:B4={2,4,1,5}. the values in column A may have 2
gt; gt; gt; gt; days in a single string and I want to add based on any one. example
gt; gt; gt; gt; sumif(A1:A4,quot;Mondayquot;,B1:B4) should give 2 and sumif(A1:A4,quot;Tuesdayquot;,B1:B4)
gt; gt; gt; gt; should also give 2.
gt; gt; gt; gt;
gt; gt; gt; gt; any help on this will be greatly appreciated...thanx a lot.
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;