Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|