Formula help
 

Hello, I need to create a formula that counts a value depending on the value
in another column (but same row). ie: count all "very"s in C only if there
is a corresponding "yes" in column A. If there is a no then disregard. (see
below) There should be 2 very, 2 not very, 1 even worse, 1 less than, 2 semi.
Ignore column B but it must be there.

A B C
yes yes very
yes no not very
no yes even worse
no no less than
yes yes semi
yes no less than
no yes semi
no no not very
yes yes even worse
no no very
yes no very
no yes even worse
yes no semi
no yes less than
yes no not very
no yes even worse

Formula help
 

Use patterns like these:

=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16="very"))
=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16="semi"))
=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16=" not very"))

etc.
--
Gary's Student
gsnu200708


"Moily" wrote:

Hello, I need to create a formula that counts a value depending on the value
in another column (but same row). ie: count all "very"s in C only if there
is a corresponding "yes" in column A. If there is a no then disregard. (see
below) There should be 2 very, 2 not very, 1 even worse, 1 less than, 2 semi.
Ignore column B but it must be there.

A B C
yes yes very
yes no not very
no yes even worse
no no less than
yes yes semi
yes no less than
no yes semi
no no not very
yes yes even worse
no no very
yes no very
no yes even worse
yes no semi
no yes less than
yes no not very
no yes even worse

Formula help
 

Hi,

Please excuse me butting in - this is nifty and works (natch).

What is the significance of the "--"s (minus minuses?)

This is rather more elegant than the DCOUNT alternative.

TIA and Regards, Matt

"Gary''s Student" wrote:

Use patterns like these:

=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16="very"))
=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16="semi"))
=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16=" not very"))

etc.
--
Gary's Student
gsnu200708


"Moily" wrote:

Hello, I need to create a formula that counts a value depending on the value
in another column (but same row). ie: count all "very"s in C only if there
is a corresponding "yes" in column A. If there is a no then disregard. (see
below) There should be 2 very, 2 not very, 1 even worse, 1 less than, 2 semi.
Ignore column B but it must be there.

A B C
yes yes very
yes no not very
no yes even worse
no no less than
yes yes semi
yes no less than
no yes semi
no no not very
yes yes even worse
no no very
yes no very
no yes even worse
yes no semi
no yes less than
yes no not very
no yes even worse

Formula help
 

=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


veryeavy wrote:

Hi,

Please excuse me butting in - this is nifty and works (natch).

What is the significance of the "--"s (minus minuses?)

This is rather more elegant than the DCOUNT alternative.

TIA and Regards, Matt

"Gary''s Student" wrote:

Use patterns like these:

=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16="very"))
=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16="semi"))
=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16=" not very"))

etc.
--
Gary's Student
gsnu200708


"Moily" wrote:

Hello, I need to create a formula that counts a value depending on the value
in another column (but same row). ie: count all "very"s in C only if there
is a corresponding "yes" in column A. If there is a no then disregard. (see
below) There should be 2 very, 2 not very, 1 even worse, 1 less than, 2 semi.
Ignore column B but it must be there.

A B C
yes yes very
yes no not very
no yes even worse
no no less than
yes yes semi
yes no less than
no yes semi
no no not very
yes yes even worse
no no very
yes no very
no yes even worse
yes no semi
no yes less than
yes no not very
no yes even worse


--

Dave Peterson

Formula help
 

Thank you very very much!!! Your answer means three days work has been
reduced to only a few hours....!

"Gary''s Student" wrote:

Use patterns like these:

=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16="very"))
=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16="semi"))
=SUMPRODUCT(--(A1:A16="yes"),--(C1:C16=" not very"))

etc.
--
Gary's Student
gsnu200708


"Moily" wrote:

Hello, I need to create a formula that counts a value depending on the value
in another column (but same row). ie: count all "very"s in C only if there
is a corresponding "yes" in column A. If there is a no then disregard. (see
below) There should be 2 very, 2 not very, 1 even worse, 1 less than, 2 semi.
Ignore column B but it must be there.

A B C
yes yes very
yes no not very
no yes even worse
no no less than
yes yes semi
yes no less than
no yes semi
no no not very
yes yes even worse
no no very
yes no very
no yes even worse
yes no semi
no yes less than
yes no not very
no yes even worse