special case of sumif

[delali]

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]

Like:

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

????

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.

 

[delali]

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]

=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]

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

=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