Re: SUMPRODUCT - 2 columns (one date one text)
From: Peter (anonymous_at_discussions.microsoft.com)
Date: 08/18/04
- Next message: Frank Kabel: "Re: additional formula ?"
- Previous message: corphealth: "additional formula ?"
- In reply to: Frank Kabel: "Re: SUMPRODUCT - 2 columns (one date one text)"
- Next in thread: Dave Peterson: "Re: SUMPRODUCT - 2 columns (one date one text)"
- Reply: Dave Peterson: "Re: SUMPRODUCT - 2 columns (one date one text)"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 18 Aug 2004 10:23:10 -0700
Frank,
I am trying to figure out how the "--" (double unary?)
works. Is there a layperson's explanation you can put up
here?
TIA.
>-----Original Message-----
>Hi
>if you also want to count 'abc' as valid value try:
>=SUMPRODUCT(--(MONTH(A1:A96)=8),--(YEAR(A1:A96)=2004),--
(LEFT(B1:B96,3)
>="abc")),
>
>SUMPRODUCT does not support wildcards directly
>
>
>--
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>"Brian" <Brian@discussions.microsoft.com> schrieb im
Newsbeitrag
>news:994CA3AD-8F7E-4FD2-8D05-7C77D0283C7F@microsoft.com...
>> Can anyone help
>>
>> I have two columns and want to count number of times
abc* (any
>charecters
>> after abc) occurs with a particular month
>>
>> Date code
>> 08/01/2004 abc
>> 08/02/2004 abc123
>> 08/02/2004 xyz
>>
>> if I use
>>
>=SUMPRODUCT(--(MONTH(A1:A96)=8),--(YEAR(A1:A96)=2004),--
(B1:B96="abc"))
>, I
>> get the correct answer = 1
>>
>> If I use
>>
>=SUMPRODUCT(--(MONTH(A1:A96)=8),--(YEAR(A1:A96)=2004),--
(B1:B96="abc*")
>) I
>> get incorrect answer = 0
>>
>> now how do I use this formula so that it will count all
codes
>begining with
>> abc (e.g. abc1, abc123 etc.. (there will be lots of
codes begining
>with abc).
>> So how can I use the wildcard * in this formula?
>>
>> Any ideas?
>
>.
>
- Next message: Frank Kabel: "Re: additional formula ?"
- Previous message: corphealth: "additional formula ?"
- In reply to: Frank Kabel: "Re: SUMPRODUCT - 2 columns (one date one text)"
- Next in thread: Dave Peterson: "Re: SUMPRODUCT - 2 columns (one date one text)"
- Reply: Dave Peterson: "Re: SUMPRODUCT - 2 columns (one date one text)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
