Countif with 1+ specifics

=SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*('[Summary.xls]Jan
04'!$J$5:$J$1000="Fax"))

I want to add E-mail, Letter and Telephone to the sencond part of
countif. Ii cant just add them with comma's like "Fax", "Email" etc,
how can this be done without having to add up several equations?


---
Message posted from http://www.ExcelForum.com/

0
1/27/2004 8:47:07 AM
excel.misc 78881 articles. 5 followers. Follow

25 Replies
442 Views

Similar Articles

[PageSpeed] 3

"ianripping >" <<ianripping.10omsh@excelforum-nospam.com> wrote...
>=SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*('[Summary.xls]Jan
>04'!$J$5:$J$1000="Fax"))
>
>I want to add E-mail, Letter and Telephone to the sencond part of
>countif. Ii cant just add them with comma's like "Fax", "Email" etc,
>how can this be done without having to add up several equations?

Do you mean you want to count rows in which col U equals 1 while col J
equals any of "Fax", "Email", etc.? If so, try

=SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)
*('[Summary.xls]Jan 04'!$J$5:$J$1000={"Fax";"Email";"etc."}))


0
hrlngrv (1990)
1/27/2004 9:01:39 AM
=SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*
('[Summary.xls]Jan04'!$J$5:$J$1000={"Fax","Email","Letter","Telephone"}))

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"ianripping >" <<ianripping.10omsh@excelforum-nospam.com> wrote in message
news:ianripping.10omsh@excelforum-nospam.com...
> =SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*('[Summary.xls]Jan
> 04'!$J$5:$J$1000="Fax"))
>
> I want to add E-mail, Letter and Telephone to the sencond part of
> countif. Ii cant just add them with comma's like "Fax", "Email" etc,
> how can this be done without having to add up several equations?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.567 / Virus Database: 358 - Release Date: 24/01/2004


0
ken.wright (2489)
1/27/2004 9:02:55 AM
Excellent. Thanks guys!


---
Message posted from http://www.ExcelForum.com/

0
1/27/2004 9:11:39 AM
Actually it doesnt seem to work.

I get the result N/A for using: -

=SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*
('[Summary.xls]Jan04'!$J$5:$J$1000={"Fax","Email","Letter","Telephone"}))


---
Message posted from http://www.ExcelForum.com/

0
1/27/2004 9:22:07 AM
Ian,

The formula works fine for me. Check the data.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ianripping >" <<ianripping.10ooet@excelforum-nospam.com> wrote in message
news:ianripping.10ooet@excelforum-nospam.com...
> Actually it doesnt seem to work.
>
> I get the result N/A for using: -
>
> =SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*
> ('[Summary.xls]Jan04'!$J$5:$J$1000={"Fax","Email","Letter","Telephone"}))
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bob.phillips1 (6510)
1/27/2004 9:50:13 AM
Hi Bob

also tried this formula. For me it works only if the number of entries
within the {} is identically to the number of rows processed. So
=SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
won't produce an error.
Though
=SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
results in the #NA error

Am I missing something???
Frank

Bob Phillips wrote:
> Ian,
>
> The formula works fine for me. Check the data.
>
>
> "ianripping >" <<ianripping.10ooet@excelforum-nospam.com> wrote in
> message news:ianripping.10ooet@excelforum-nospam.com...
>> Actually it doesnt seem to work.
>>
>> I get the result N/A for using: -
>>
>> =SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*
>>
('[Summary.xls]Jan04'!$J$5:$J$1000={"Fax","Email","Letter","Telephone"}
))
>>
>>
>> ---
>> Message posted from http://www.ExcelForum.com/


0
frank.kabel (11126)
1/27/2004 11:57:29 AM
Hi Frank,

I am surprised. Both of your examples work for me. I put 1,1,2 in A1:A3,
Fax,"",Email in B1:B3, and the both formulae return 1. I haven't yet forced
a #N/A. I wouldn't expect SUMPRODUCT to bomb out on empty data. I tried
various combinations in row 4, and still did not get an error.

Bob

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
> Hi Bob
>
> also tried this formula. For me it works only if the number of entries
> within the {} is identically to the number of rows processed. So
> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
> won't produce an error.
> Though
> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
> results in the #NA error
>
> Am I missing something???
> Frank
>


0
bob.phillips1 (6510)
1/27/2004 12:08:53 PM
Hi Bob,
if you like I send you an example worksheet with both functions (just
give me your email address). The only reason for this different
behaviour could be my non english version (and yes I replaced the ','
with ';') but this would also be quite strange.

Frank
NTW i'm using Excel 2003


Bob Phillips wrote:
> Hi Frank,
>
> I am surprised. Both of your examples work for me. I put 1,1,2 in
> A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
> haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb out
> on empty data. I tried various combinations in row 4, and still did
> not get an error.
>
> Bob
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
>> Hi Bob
>>
>> also tried this formula. For me it works only if the number of
>> entries within the {} is identically to the number of rows
>> processed. So
>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
>> won't produce an error.
>> Though
>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
>> results in the #NA error
>>
>> Am I missing something???
>> Frank


0
frank.kabel (11126)
1/27/2004 12:19:25 PM
The only way I get #NA is by having an #NA in the ranges being evaluated.
Blanks don't affect the formula at all my end.  There will be a #VALUE error if
there is any text in the field containing the 1s, eg a header row being included
in the ranges, OR if any of the blanks are really spaces perhaps??

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%235n8V5M5DHA.2480@TK2MSFTNGP10.phx.gbl...
> Hi Frank,
>
> I am surprised. Both of your examples work for me. I put 1,1,2 in A1:A3,
> Fax,"",Email in B1:B3, and the both formulae return 1. I haven't yet forced
> a #N/A. I wouldn't expect SUMPRODUCT to bomb out on empty data. I tried
> various combinations in row 4, and still did not get an error.
>
> Bob
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
> > Hi Bob
> >
> > also tried this formula. For me it works only if the number of entries
> > within the {} is identically to the number of rows processed. So
> > =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
> > won't produce an error.
> > Though
> > =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
> > results in the #NA error
> >
> > Am I missing something???
> > Frank
> >
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
ken.wright (2489)
1/27/2004 12:27:36 PM
Hi Ken
Thats getting really interesting. In my Tesdata:
- column A only consists of numbers (double checked with ISNUMBER,
etc.)
- There are no #NA errors in either column A or B
- There are no spaces or blanks

So I really want to undersatnd this behaviour (quite curious now). I
can send you my small test workbook if you like.
Frank


Ken Wright wrote:
> The only way I get #NA is by having an #NA in the ranges being
> evaluated. Blanks don't affect the formula at all my end.  There will
> be a #VALUE error if there is any text in the field containing the
> 1s, eg a header row being included in the ranges, OR if any of the
> blanks are really spaces perhaps??
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%235n8V5M5DHA.2480@TK2MSFTNGP10.phx.gbl...
>> Hi Frank,
>>
>> I am surprised. Both of your examples work for me. I put 1,1,2 in
>> A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
>> haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb out
>> on empty data. I tried various combinations in row 4, and still did
>> not get an error.
>>
>> Bob
>>
>> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
>> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
>>> Hi Bob
>>>
>>> also tried this formula. For me it works only if the number of
>>> entries within the {} is identically to the number of rows
>>> processed. So
>>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
>>> won't produce an error.
>>> Though
>>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
>>> results in the #NA error
>>>
>>> Am I missing something???
>>> Frank
>>>
>>
>>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
frank.kabel (11126)
1/27/2004 12:33:36 PM
Frank,

Yes do send it to

bob . phillips @ tiscali . co . uk

remove all the spaces

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23ha2P$M5DHA.1368@TK2MSFTNGP10.phx.gbl...
> Hi Bob,
> if you like I send you an example worksheet with both functions (just
> give me your email address). The only reason for this different
> behaviour could be my non english version (and yes I replaced the ','
> with ';') but this would also be quite strange.
>
> Frank
> NTW i'm using Excel 2003
>
>
> Bob Phillips wrote:
> > Hi Frank,
> >
> > I am surprised. Both of your examples work for me. I put 1,1,2 in
> > A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
> > haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb out
> > on empty data. I tried various combinations in row 4, and still did
> > not get an error.
> >
> > Bob
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
> >> Hi Bob
> >>
> >> also tried this formula. For me it works only if the number of
> >> entries within the {} is identically to the number of rows
> >> processed. So
> >> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
> >> won't produce an error.
> >> Though
> >> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
> >> results in the #NA error
> >>
> >> Am I missing something???
> >> Frank
>
>


0
bob.phillips1 (6510)
1/27/2004 12:50:59 PM
Hi Frank - Send it on down.  Also happy to mail you out my sample sheet to see
if that also produces the same error on your system if you like.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%2361$KHN5DHA.2168@TK2MSFTNGP12.phx.gbl...
> Hi Ken
> Thats getting really interesting. In my Tesdata:
> - column A only consists of numbers (double checked with ISNUMBER,
> etc.)
> - There are no #NA errors in either column A or B
> - There are no spaces or blanks
>
> So I really want to undersatnd this behaviour (quite curious now). I
> can send you my small test workbook if you like.
> Frank
>
>
> Ken Wright wrote:
> > The only way I get #NA is by having an #NA in the ranges being
> > evaluated. Blanks don't affect the formula at all my end.  There will
> > be a #VALUE error if there is any text in the field containing the
> > 1s, eg a header row being included in the ranges, OR if any of the
> > blanks are really spaces perhaps??
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:%235n8V5M5DHA.2480@TK2MSFTNGP10.phx.gbl...
> >> Hi Frank,
> >>
> >> I am surprised. Both of your examples work for me. I put 1,1,2 in
> >> A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
> >> haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb out
> >> on empty data. I tried various combinations in row 4, and still did
> >> not get an error.
> >>
> >> Bob
> >>
> >> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> >> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
> >>> Hi Bob
> >>>
> >>> also tried this formula. For me it works only if the number of
> >>> entries within the {} is identically to the number of rows
> >>> processed. So
> >>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
> >>> won't produce an error.
> >>> Though
> >>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
> >>> results in the #NA error
> >>>
> >>> Am I missing something???
> >>> Frank
> >>>
> >>
> >>
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
ken.wright (2489)
1/27/2004 12:53:05 PM
Frank,

It seems to be the ; delimiter in the array. If there are the same number of
items as rows being tested it works okay, else it #N/A, even over here. It
even fails if you have too many items in the array.

Whereas, a ' delimiter works in all cases. I have sent you a screenshot
direct to demonstrate it.

Why don 't you change those #N/A formulae to have a comma delimiter. I know
continental Excel delimits with ; but arrays may be different. Give it a
try.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23ha2P$M5DHA.1368@TK2MSFTNGP10.phx.gbl...
> Hi Bob,
> if you like I send you an example worksheet with both functions (just
> give me your email address). The only reason for this different
> behaviour could be my non english version (and yes I replaced the ','
> with ';') but this would also be quite strange.
>
> Frank
> NTW i'm using Excel 2003
>
>
> Bob Phillips wrote:
> > Hi Frank,
> >
> > I am surprised. Both of your examples work for me. I put 1,1,2 in
> > A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
> > haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb out
> > on empty data. I tried various combinations in row 4, and still did
> > not get an error.
> >
> > Bob
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
> >> Hi Bob
> >>
> >> also tried this formula. For me it works only if the number of
> >> entries within the {} is identically to the number of rows
> >> processed. So
> >> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
> >> won't produce an error.
> >> Though
> >> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
> >> results in the #NA error
> >>
> >> Am I missing something???
> >> Frank
>
>


0
bob.phillips1 (6510)
1/27/2004 1:38:18 PM
It's the delimiter being used.  You have a ; in there as opposed to my ,

Switching them works fine for me.  If I put a ; in there it seems to try and
evaluate the range against what is effectively a range within the braces, ie
{"Email";"Fax","Phone"}

With the following data:-

       A       B
1      1    Email
2      1    Fax
3      1    Phone

=SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Email";"Fax";"Phone"})) is effectively
doing:-

        A           B
1     =1 (yes)   *   ={Email  (yes)   = 1
2     =1 (yes)   *   =Fax      (yes)   = 1
3     =1 (yes)   *   =Phone} (yes)   = 1
----------------------------------------------------
SUM                                         =  3

evaluating the data within the braces as though it were a range, just like the
A1:A3 or B1:B3, which because it happens to have the same amount of arguments as
there are rows will give you effectively an equal length range, so the formula
appears to work, although if you switch the arguments around you will see in
fact that it is not giving you what you want, eg:-

=SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Phone";"Fax";"Email"})) gives you:-

        A           B
1     =1 (yes)  *   ={Email  (no)   = 0
2     =1 (yes)  *   =Fax     (yes)  = 1
3     =1 (yes)  *   =Phone}  (no)  = 0
------------------------------------------
SUM                                       =  1


and so because there are only 3 arguments, if you increase the ranges to 4 rows,
there is as far as the formula is concerned a range of unequal length in the
formula:-

=SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Email";"Fax";"Phone"})) is effectively
doing:-

        A           B
1     =1 (yes)  *   ={Email (yes)   = 1
2     =1 (yes)  *   =Fax     (yes)   = 1
3     =1 (yes)  *   =Phone (yes)   = 1
4     =1 (yes)  *   =#N/A } (oops)  =#N/A
----------------------------------------------
SUM                                         = #N/A

whereas you really want the formula to evaluate each row in Col B for all the
options in the braces, so using a comma as delimiter will make the arguments a
horizontal array of data and evaluate each row against each item within the
array, eg:-

        A           B
1     =1   *   ={Email or Fax or Phone}  (yes)    = 1
2     =1   *   ={Email or Fax or Phone}  (yes)    = 1
3     =1   *   ={Email or Fax or Phone}  (yes)    = 1
4     =1   *   ={Email or Fax or Phone}  (yes)    = 1
-------------------------------------
SUM                                                            = 4

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%2361$KHN5DHA.2168@TK2MSFTNGP12.phx.gbl...
> Hi Ken
> Thats getting really interesting. In my Tesdata:
> - column A only consists of numbers (double checked with ISNUMBER,
> etc.)
> - There are no #NA errors in either column A or B
> - There are no spaces or blanks
>
> So I really want to undersatnd this behaviour (quite curious now). I
> can send you my small test workbook if you like.
> Frank
>
>
> Ken Wright wrote:
> > The only way I get #NA is by having an #NA in the ranges being
> > evaluated. Blanks don't affect the formula at all my end.  There will
> > be a #VALUE error if there is any text in the field containing the
> > 1s, eg a header row being included in the ranges, OR if any of the
> > blanks are really spaces perhaps??
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:%235n8V5M5DHA.2480@TK2MSFTNGP10.phx.gbl...
> >> Hi Frank,
> >>
> >> I am surprised. Both of your examples work for me. I put 1,1,2 in
> >> A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
> >> haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb out
> >> on empty data. I tried various combinations in row 4, and still did
> >> not get an error.
> >>
> >> Bob
> >>
> >> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> >> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
> >>> Hi Bob
> >>>
> >>> also tried this formula. For me it works only if the number of
> >>> entries within the {} is identically to the number of rows
> >>> processed. So
> >>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
> >>> won't produce an error.
> >>> Though
> >>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
> >>> results in the #NA error
> >>>
> >>> Am I missing something???
> >>> Frank
> >>>
> >>
> >>
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
ken.wright (2489)
1/27/2004 1:38:55 PM
Exactly the same conclusion I came to, but weird or what?

Bob


"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:uFVorrN5DHA.2344@TK2MSFTNGP09.phx.gbl...
> It's the delimiter being used.  You have a ; in there as opposed to my ,
>
> Switching them works fine for me.  If I put a ; in there it seems to try
and
> evaluate the range against what is effectively a range within the braces,
ie
> {"Email";"Fax","Phone"}
>
> With the following data:-
>
>        A       B
> 1      1    Email
> 2      1    Fax
> 3      1    Phone
>
> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Email";"Fax";"Phone"})) is
effectively
> doing:-
>
>         A           B
> 1     =1 (yes)   *   ={Email  (yes)   = 1
> 2     =1 (yes)   *   =Fax      (yes)   = 1
> 3     =1 (yes)   *   =Phone} (yes)   = 1
> ----------------------------------------------------
> SUM                                         =  3
>
> evaluating the data within the braces as though it were a range, just like
the
> A1:A3 or B1:B3, which because it happens to have the same amount of
arguments as
> there are rows will give you effectively an equal length range, so the
formula
> appears to work, although if you switch the arguments around you will see
in
> fact that it is not giving you what you want, eg:-
>
> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Phone";"Fax";"Email"})) gives you:-
>
>         A           B
> 1     =1 (yes)  *   ={Email  (no)   = 0
> 2     =1 (yes)  *   =Fax     (yes)  = 1
> 3     =1 (yes)  *   =Phone}  (no)  = 0
> ------------------------------------------
> SUM                                       =  1
>
>
> and so because there are only 3 arguments, if you increase the ranges to 4
rows,
> there is as far as the formula is concerned a range of unequal length in
the
> formula:-
>
> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Email";"Fax";"Phone"})) is
effectively
> doing:-
>
>         A           B
> 1     =1 (yes)  *   ={Email (yes)   = 1
> 2     =1 (yes)  *   =Fax     (yes)   = 1
> 3     =1 (yes)  *   =Phone (yes)   = 1
> 4     =1 (yes)  *   =#N/A } (oops)  =#N/A
> ----------------------------------------------
> SUM                                         = #N/A
>
> whereas you really want the formula to evaluate each row in Col B for all
the
> options in the braces, so using a comma as delimiter will make the
arguments a
> horizontal array of data and evaluate each row against each item within
the
> array, eg:-
>
>         A           B
> 1     =1   *   ={Email or Fax or Phone}  (yes)    = 1
> 2     =1   *   ={Email or Fax or Phone}  (yes)    = 1
> 3     =1   *   ={Email or Fax or Phone}  (yes)    = 1
> 4     =1   *   ={Email or Fax or Phone}  (yes)    = 1
> -------------------------------------
> SUM                                                            = 4
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
>
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:%2361$KHN5DHA.2168@TK2MSFTNGP12.phx.gbl...
> > Hi Ken
> > Thats getting really interesting. In my Tesdata:
> > - column A only consists of numbers (double checked with ISNUMBER,
> > etc.)
> > - There are no #NA errors in either column A or B
> > - There are no spaces or blanks
> >
> > So I really want to undersatnd this behaviour (quite curious now). I
> > can send you my small test workbook if you like.
> > Frank
> >
> >
> > Ken Wright wrote:
> > > The only way I get #NA is by having an #NA in the ranges being
> > > evaluated. Blanks don't affect the formula at all my end.  There will
> > > be a #VALUE error if there is any text in the field containing the
> > > 1s, eg a header row being included in the ranges, OR if any of the
> > > blanks are really spaces perhaps??
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:%235n8V5M5DHA.2480@TK2MSFTNGP10.phx.gbl...
> > >> Hi Frank,
> > >>
> > >> I am surprised. Both of your examples work for me. I put 1,1,2 in
> > >> A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
> > >> haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb out
> > >> on empty data. I tried various combinations in row 4, and still did
> > >> not get an error.
> > >>
> > >> Bob
> > >>
> > >> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > >> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
> > >>> Hi Bob
> > >>>
> > >>> also tried this formula. For me it works only if the number of
> > >>> entries within the {} is identically to the number of rows
> > >>> processed. So
> > >>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
> > >>> won't produce an error.
> > >>> Though
> > >>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
> > >>> results in the #NA error
> > >>>
> > >>> Am I missing something???
> > >>> Frank
> > >>>
> > >>
> > >>
> > >
> > >
> > > ---
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
>
>


0
bob.phillips1 (6510)
1/27/2004 1:46:05 PM
Hi Bob and Ken

now we have the solution. Excel is really strange in respect to
regional settings. Normally you have to replace the [,] with the [;] in
functions, etc. So in this case Excel does not accept the [,] in lists
entered in {}. So {"Fax","Phone"} is not allowed in continental
versions of Excel.

But looking at Ken's spreadsheet Excel converts his [,] in the list to
[.]. So after I changed {"Fax";"Phone"} to {"Fax"."Phone"} everything
works fine!!!!!!
So it was really the regional specifics of Excel. Unfortunately this is
not mentioned in any helpfile, etc. and differing from all other
instances Excel required a [.] and not a [;]

Thanks both of you helping me to solve this issue
Frank


Bob Phillips wrote:
> Frank,
>
> It seems to be the ; delimiter in the array. If there are the same
> number of items as rows being tested it works okay, else it #N/A,
> even over here. It even fails if you have too many items in the
array.
>
> Whereas, a ' delimiter works in all cases. I have sent you a
> screenshot direct to demonstrate it.
>
> Why don 't you change those #N/A formulae to have a comma delimiter.
> I know continental Excel delimits with ; but arrays may be different.
> Give it a try.
>
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:%23ha2P$M5DHA.1368@TK2MSFTNGP10.phx.gbl...
>> Hi Bob,
>> if you like I send you an example worksheet with both functions
(just
>> give me your email address). The only reason for this different
>> behaviour could be my non english version (and yes I replaced the
','
>> with ';') but this would also be quite strange.
>>
>> Frank
>> NTW i'm using Excel 2003
>>
>>
>> Bob Phillips wrote:
>>> Hi Frank,
>>>
>>> I am surprised. Both of your examples work for me. I put 1,1,2 in
>>> A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
>>> haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb out
>>> on empty data. I tried various combinations in row 4, and still did
>>> not get an error.
>>>
>>> Bob
>>>
>>> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
>>> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
>>>> Hi Bob
>>>>
>>>> also tried this formula. For me it works only if the number of
>>>> entries within the {} is identically to the number of rows
>>>> processed. So
>>>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
>>>> won't produce an error.
>>>> Though
>>>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
>>>> results in the #NA error
>>>>
>>>> Am I missing something???
>>>> Frank


0
frank.kabel (11126)
1/27/2004 1:50:22 PM
LOL - and identically timed posts as well. Cue the spooky music - Doo Doo Doo
Doo, Doo Doo Doo Doo  :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23KbvqvN5DHA.1804@TK2MSFTNGP12.phx.gbl...
> Exactly the same conclusion I came to, but weird or what?
>
> Bob
<snip>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
ken.wright (2489)
1/27/2004 1:50:47 PM
Hi Bob
all this problems won't happen if Microsoft included an option to use a
standard set of delimiters, function names, etc (the english version).
regardless of the regional Excel version.
Frank

Bob Phillips wrote:
> Exactly the same conclusion I came to, but weird or what?
>
> Bob
>
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> news:uFVorrN5DHA.2344@TK2MSFTNGP09.phx.gbl...
>> It's the delimiter being used.  You have a ; in there as opposed to
>> my ,
>>
>> Switching them works fine for me.  If I put a ; in there it seems to
>> try and evaluate the range against what is effectively a range
>> within the braces, ie {"Email";"Fax","Phone"}
>>
>> With the following data:-
>>
>>        A       B
>> 1      1    Email
>> 2      1    Fax
>> 3      1    Phone
>>
>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Email";"Fax";"Phone"})) is
>> effectively doing:-
>>
>>         A           B
>> 1     =1 (yes)   *   ={Email  (yes)   = 1
>> 2     =1 (yes)   *   =Fax      (yes)   = 1
>> 3     =1 (yes)   *   =Phone} (yes)   = 1
>> ----------------------------------------------------
>> SUM                                         =  3
>>
>> evaluating the data within the braces as though it were a range,
>> just like the A1:A3 or B1:B3, which because it happens to have the
>> same amount of arguments as there are rows will give you effectively
>> an equal length range, so the formula appears to work, although if
>> you switch the arguments around you will see in fact that it is not
>> giving you what you want, eg:-
>>
>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Phone";"Fax";"Email"})) gives
>> you:-
>>
>>         A           B
>> 1     =1 (yes)  *   ={Email  (no)   = 0
>> 2     =1 (yes)  *   =Fax     (yes)  = 1
>> 3     =1 (yes)  *   =Phone}  (no)  = 0
>> ------------------------------------------
>> SUM                                       =  1
>>
>>
>> and so because there are only 3 arguments, if you increase the
>> ranges to 4 rows, there is as far as the formula is concerned a
>> range of unequal length in the formula:-
>>
>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Email";"Fax";"Phone"})) is
>> effectively doing:-
>>
>>         A           B
>> 1     =1 (yes)  *   ={Email (yes)   = 1
>> 2     =1 (yes)  *   =Fax     (yes)   = 1
>> 3     =1 (yes)  *   =Phone (yes)   = 1
>> 4     =1 (yes)  *   =#N/A } (oops)  =#N/A
>> ----------------------------------------------
>> SUM                                         = #N/A
>>
>> whereas you really want the formula to evaluate each row in Col B
>> for all the options in the braces, so using a comma as delimiter
>> will make the arguments a horizontal array of data and evaluate each
>> row against each item within the array, eg:-
>>
>>         A           B
>> 1     =1   *   ={Email or Fax or Phone}  (yes)    = 1
>> 2     =1   *   ={Email or Fax or Phone}  (yes)    = 1
>> 3     =1   *   ={Email or Fax or Phone}  (yes)    = 1
>> 4     =1   *   ={Email or Fax or Phone}  (yes)    = 1
>> -------------------------------------
>> SUM                                                            = 4
>>
>> --
>> Regards
>>            Ken.......................    Microsoft MVP - Excel
>>                   Sys Spec - Win XP Pro /  XL 00/02/03
>>
>> --------------------------------------------------------------------
------
>> -- It's easier to beg forgiveness than ask permission :-)
>> --------------------------------------------------------------------
------
>>
>>
>>
>> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
>> news:%2361$KHN5DHA.2168@TK2MSFTNGP12.phx.gbl...
>>> Hi Ken
>>> Thats getting really interesting. In my Tesdata:
>>> - column A only consists of numbers (double checked with ISNUMBER,
>>> etc.)
>>> - There are no #NA errors in either column A or B
>>> - There are no spaces or blanks
>>>
>>> So I really want to undersatnd this behaviour (quite curious now).
I
>>> can send you my small test workbook if you like.
>>> Frank
>>>
>>>
>>> Ken Wright wrote:
>>>> The only way I get #NA is by having an #NA in the ranges being
>>>> evaluated. Blanks don't affect the formula at all my end.  There
>>>> will be a #VALUE error if there is any text in the field
>>>> containing the 1s, eg a header row being included in the ranges,
>>>> OR if any of the blanks are really spaces perhaps??
>>>>
>>>>
>>>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
message
>>>> news:%235n8V5M5DHA.2480@TK2MSFTNGP10.phx.gbl...
>>>>> Hi Frank,
>>>>>
>>>>> I am surprised. Both of your examples work for me. I put 1,1,2 in
>>>>> A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
>>>>> haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb
>>>>> out on empty data. I tried various combinations in row 4, and
>>>>> still did not get an error.
>>>>>
>>>>> Bob
>>>>>
>>>>> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
>>>>> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
>>>>>> Hi Bob
>>>>>>
>>>>>> also tried this formula. For me it works only if the number of
>>>>>> entries within the {} is identically to the number of rows
>>>>>> processed. So
>>>>>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
>>>>>> won't produce an error.
>>>>>> Though
>>>>>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
>>>>>> results in the #NA error
>>>>>>
>>>>>> Am I missing something???
>>>>>> Frank
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> ---
>>>> Outgoing mail is certified Virus Free.
>>>> Checked by AVG anti-virus system (http://www.grisoft.com).
>>>> Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
>>>
>>>
>>
>>
>> ---
>> Outgoing mail is certified Virus Free.
>> Checked by AVG anti-virus system (http://www.grisoft.com).
>> Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
frank.kabel (11126)
1/27/2004 1:54:40 PM
Yeah, we've had fun, but Ian still has his problem?

Bob

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:%23JcOUyN5DHA.2760@TK2MSFTNGP09.phx.gbl...
> LOL - and identically timed posts as well. Cue the spooky music - Doo Doo
Doo
> Doo, Doo Doo Doo Doo  :-)
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23KbvqvN5DHA.1804@TK2MSFTNGP12.phx.gbl...
> > Exactly the same conclusion I came to, but weird or what?
> >
> > Bob
> <snip>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
>
>


0
bob.phillips1 (6510)
1/27/2004 1:55:35 PM
Frank,

You are right, but I suppose it could be argued that MS was showing some
cultural sensitivity by recognising that continental Europe uses , and . in
the opposite sense to the US and UK (God knows it could do with some on
dates).

But if , is required as the delimiter for embedded arrays, then why allow ;
which works in a different, undocumented (AFAIK) manner. Consistency seems
to be lacking as in many other instances.

Anyway, it's been a good discussion with you Frank, enjoyed it.As I said, I
wonder if Ian is sorted!

Regards

Bob

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:%23e6yd0N5DHA.3896@TK2MSFTNGP11.phx.gbl...
> Hi Bob
> all this problems won't happen if Microsoft included an option to use a
> standard set of delimiters, function names, etc (the english version).
> regardless of the regional Excel version.
> Frank
>
> Bob Phillips wrote:
> > Exactly the same conclusion I came to, but weird or what?
> >
> > Bob
> >
> >
> > "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> > news:uFVorrN5DHA.2344@TK2MSFTNGP09.phx.gbl...
> >> It's the delimiter being used.  You have a ; in there as opposed to
> >> my ,
> >>
> >> Switching them works fine for me.  If I put a ; in there it seems to
> >> try and evaluate the range against what is effectively a range
> >> within the braces, ie {"Email";"Fax","Phone"}
> >>
> >> With the following data:-
> >>
> >>        A       B
> >> 1      1    Email
> >> 2      1    Fax
> >> 3      1    Phone
> >>
> >> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Email";"Fax";"Phone"})) is
> >> effectively doing:-
> >>
> >>         A           B
> >> 1     =1 (yes)   *   ={Email  (yes)   = 1
> >> 2     =1 (yes)   *   =Fax      (yes)   = 1
> >> 3     =1 (yes)   *   =Phone} (yes)   = 1
> >> ----------------------------------------------------
> >> SUM                                         =  3
> >>
> >> evaluating the data within the braces as though it were a range,
> >> just like the A1:A3 or B1:B3, which because it happens to have the
> >> same amount of arguments as there are rows will give you effectively
> >> an equal length range, so the formula appears to work, although if
> >> you switch the arguments around you will see in fact that it is not
> >> giving you what you want, eg:-
> >>
> >> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Phone";"Fax";"Email"})) gives
> >> you:-
> >>
> >>         A           B
> >> 1     =1 (yes)  *   ={Email  (no)   = 0
> >> 2     =1 (yes)  *   =Fax     (yes)  = 1
> >> 3     =1 (yes)  *   =Phone}  (no)  = 0
> >> ------------------------------------------
> >> SUM                                       =  1
> >>
> >>
> >> and so because there are only 3 arguments, if you increase the
> >> ranges to 4 rows, there is as far as the formula is concerned a
> >> range of unequal length in the formula:-
> >>
> >> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Email";"Fax";"Phone"})) is
> >> effectively doing:-
> >>
> >>         A           B
> >> 1     =1 (yes)  *   ={Email (yes)   = 1
> >> 2     =1 (yes)  *   =Fax     (yes)   = 1
> >> 3     =1 (yes)  *   =Phone (yes)   = 1
> >> 4     =1 (yes)  *   =#N/A } (oops)  =#N/A
> >> ----------------------------------------------
> >> SUM                                         = #N/A
> >>
> >> whereas you really want the formula to evaluate each row in Col B
> >> for all the options in the braces, so using a comma as delimiter
> >> will make the arguments a horizontal array of data and evaluate each
> >> row against each item within the array, eg:-
> >>
> >>         A           B
> >> 1     =1   *   ={Email or Fax or Phone}  (yes)    = 1
> >> 2     =1   *   ={Email or Fax or Phone}  (yes)    = 1
> >> 3     =1   *   ={Email or Fax or Phone}  (yes)    = 1
> >> 4     =1   *   ={Email or Fax or Phone}  (yes)    = 1
> >> -------------------------------------
> >> SUM                                                            = 4
> >>
> >> --
> >> Regards
> >>            Ken.......................    Microsoft MVP - Excel
> >>                   Sys Spec - Win XP Pro /  XL 00/02/03
> >>
> >> --------------------------------------------------------------------
> ------
> >> -- It's easier to beg forgiveness than ask permission :-)
> >> --------------------------------------------------------------------
> ------
> >>
> >>
> >>
> >> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> >> news:%2361$KHN5DHA.2168@TK2MSFTNGP12.phx.gbl...
> >>> Hi Ken
> >>> Thats getting really interesting. In my Tesdata:
> >>> - column A only consists of numbers (double checked with ISNUMBER,
> >>> etc.)
> >>> - There are no #NA errors in either column A or B
> >>> - There are no spaces or blanks
> >>>
> >>> So I really want to undersatnd this behaviour (quite curious now).
> I
> >>> can send you my small test workbook if you like.
> >>> Frank
> >>>
> >>>
> >>> Ken Wright wrote:
> >>>> The only way I get #NA is by having an #NA in the ranges being
> >>>> evaluated. Blanks don't affect the formula at all my end.  There
> >>>> will be a #VALUE error if there is any text in the field
> >>>> containing the 1s, eg a header row being included in the ranges,
> >>>> OR if any of the blanks are really spaces perhaps??
> >>>>
> >>>>
> >>>> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in
> message
> >>>> news:%235n8V5M5DHA.2480@TK2MSFTNGP10.phx.gbl...
> >>>>> Hi Frank,
> >>>>>
> >>>>> I am surprised. Both of your examples work for me. I put 1,1,2 in
> >>>>> A1:A3, Fax,"",Email in B1:B3, and the both formulae return 1. I
> >>>>> haven't yet forced a #N/A. I wouldn't expect SUMPRODUCT to bomb
> >>>>> out on empty data. I tried various combinations in row 4, and
> >>>>> still did not get an error.
> >>>>>
> >>>>> Bob
> >>>>>
> >>>>> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> >>>>> news:O8AFAzM5DHA.504@TK2MSFTNGP11.phx.gbl...
> >>>>>> Hi Bob
> >>>>>>
> >>>>>> also tried this formula. For me it works only if the number of
> >>>>>> entries within the {} is identically to the number of rows
> >>>>>> processed. So
> >>>>>> =SUMPRODUCT(($A$1:$A$3=1)*($B$1:$B$3={"Fax","Email","Phone"}))
> >>>>>> won't produce an error.
> >>>>>> Though
> >>>>>> =SUMPRODUCT(($A$1:$A$4=1)*($B$1:$B$4={"Fax","Email","Phone"}))
> >>>>>> results in the #NA error
> >>>>>>
> >>>>>> Am I missing something???
> >>>>>> Frank
> >>>>>>
> >>>>>
> >>>>>
> >>>>
> >>>>
> >>>> ---
> >>>> Outgoing mail is certified Virus Free.
> >>>> Checked by AVG anti-virus system (http://www.grisoft.com).
> >>>> Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
> >>>
> >>>
> >>
> >>
> >> ---
> >> Outgoing mail is certified Virus Free.
> >> Checked by AVG anti-virus system (http://www.grisoft.com).
> >> Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
>
>


0
bob.phillips1 (6510)
1/27/2004 2:04:24 PM
Ian, are you able to mail out a copy of your worksheet?

ken.wright@NOSPAMntlworld

without the NOSPAM bit of course :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"ianripping >" <<ianripping.10ooet@excelforum-nospam.com> wrote in message
news:ianripping.10ooet@excelforum-nospam.com...
> Actually it doesnt seem to work.
>
> I get the result N/A for using: -
>
> =SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*
> ('[Summary.xls]Jan04'!$J$5:$J$1000={"Fax","Email","Letter","Telephone"}))
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
ken.wright (2489)
1/27/2004 2:41:36 PM
rotflmao - Had forgotten it was Ian that started the thread - Glad you reminded
me  :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ODuA%230N5DHA.2392@TK2MSFTNGP11.phx.gbl...
> Yeah, we've had fun, but Ian still has his problem?
>
> Bob
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> news:%23JcOUyN5DHA.2760@TK2MSFTNGP09.phx.gbl...
> > LOL - and identically timed posts as well. Cue the spooky music - Doo Doo
> Doo
> > Doo, Doo Doo Doo Doo  :-)
> >
> > -- 
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                   Sys Spec - Win XP Pro /  XL 00/02/03
> >
> > --------------------------------------------------------------------------
> --
> > It's easier to beg forgiveness than ask permission :-)
> > --------------------------------------------------------------------------
> --
> >
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:%23KbvqvN5DHA.1804@TK2MSFTNGP12.phx.gbl...
> > > Exactly the same conclusion I came to, but weird or what?
> > >
> > > Bob
> > <snip>
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
> >
> >
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
ken.wright (2489)
1/27/2004 2:42:26 PM
Doh - missed the .com bit -  ken.wright@NOSPAMntlworld.com

without the NOSPAM bit of course :-)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:OIU8sOO5DHA.2776@TK2MSFTNGP09.phx.gbl...
> Ian, are you able to mail out a copy of your worksheet?
>
> ken.wright@NOSPAMntlworld
>
> without the NOSPAM bit of course :-)
>
> -- 
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
>
>
> "ianripping >" <<ianripping.10ooet@excelforum-nospam.com> wrote in message
> news:ianripping.10ooet@excelforum-nospam.com...
> > Actually it doesnt seem to work.
> >
> > I get the result N/A for using: -
> >
> > =SUMPRODUCT(('[Summary.xls]Jan 04'!$U$5:$U$1000=1)*
> > ('[Summary.xls]Jan04'!$J$5:$J$1000={"Fax","Email","Letter","Telephone"}))
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004


0
ken.wright (2489)
1/27/2004 2:43:35 PM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote...
>It seems to be the ; delimiter in the array. If there are the same number
of
>items as rows being tested it works okay, else it #N/A, even over here. It
>even fails if you have too many items in the array.
....

If ; is the list separator, then using it with the same number of entries as
are in the range doesn't provide the same result as using ; in US settings
and different number of entries.


0
hrlngrv (1990)
1/27/2004 4:05:37 PM
Hi Bob
[snip]
> Anyway, it's been a good discussion with you Frank, enjoyed it.As I
> said, I wonder if Ian is sorted!

maybe he had the same problem with using a different delimiter. 
Frank
0
frank.kabel (11126)
1/27/2004 4:57:50 PM
Reply:

Similar Artilces:

How can i print 1 page only??
We have Microsoft Outlook 2002. If an e-mail initially generated by us becomes very long owing to back and forth replies we would like to print only the first page containing the latest reply. However, the only option we are given is to print 'all/even/odd' pages. Some mails we receive which are not initially generated by us however give us the option of which page to print. It is this type of print option we would like to have for all e-mails! its driving us up the wall, there must be a simple way to do it but we just cant find out how! HELP! --------------------------------...

mailroot vsi 1 directory not responding
I can browse to the c:\program files\exchsrvr\mailroot but once I click on "vsi 1" my explorer quit responding. What prompted me to check was my exchange HD was running out of disk space and I believe the bad mail location is building up and was never deleted. Any help is much appreciated! Did you try from a command line? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "chris" <chris@discussions.microsoft.com> wrote in message news:23626045-BE0D-4425-B55E-DCA017AA5B03@microsoft.com... >I can browse to the c:\prog...

Full Screen Mode in CRM 1.2
Is it possible to open by default the new windows of MS CRM (ex: the account detail form) in full screen mode? Thanks for your answer ...

Virus in the Mailroot\vsi 1\queue.... can I delete ???
ScanMail has detected the virus HTML_Netsky.P in 4 messages that are held in the Mailroot\vsi 1\queue is it ok to delete these without any reprocussions? ScanMail is unable to remove. The best practice against a virus y delete this. For this use the System Manager to delete directly at the queue icon -- Jorge Patricio D�az Guzm�n MCP http://www.itpro.cl This posting is provided "AS IS" with no warranties, and confers no rights. "Penny Miller" <Penny.Miller@co.chelan.wa.us> escribi� en el mensaje news:Oq3O%23YvWFHA.2080@TK2MSFTNGP15.phx.gbl... > ScanMa...

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

Outlook 2003 won't send 1 message
I'm having the same problem as Joshua..and it just started today after I loaded a lot of Windows XP Professional updates off the Microsoft site. One message is hung in my outbox; if I write a new message, it moves to the hung position and won't send. Any ideas? I am having the same problem with Outlook 2003. I just can't send the message. Anyone have any ideas? "kenc" <anonymous@discussions.microsoft.com> wrote in message news:085601c3bc08$e6e7ff70$a301280a@phx.gbl... > I'm having the same problem as Joshua..and it just > started today after I loa...

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Adding ore licenses to MSCRM 1.2
Hi, We have MSCRM 1.2 configured in our organization with 10 licenses.Recently we have got 10 more licenses and we need to add these to the current MSCRM server 1.2 server. What is the procedure to do that. Regards, Raju Hi, You can use the deployment manager to do this. The deployment manager contains a license manager where you can add and edit licenses. Rob Bakkers, Avanade Netherlands "Raju" wrote: > Hi, > > We have MSCRM 1.2 configured in our organization > with 10 licenses.Recently we have got 10 more licenses and we need > to add the...

Register specific items?
Is it possible to set up specific items to separate registers? I have two registers and would like to have certain items be listed on each, but not on both. Is this a possibility? Thanks No. All items will be available at all registers. You might take a look at the touch screen keyboard feature - it will work with a mouse just as well as with a touch screen. You can have a different touch screen keyboard assigned to each register with a separate set of keys. Touchscreen keys can point to a specific department, category, supplier or specific item as well as replacing the regular f...

Getting Error while Upgrading from VC7.1 to VC8..
Hi I am Upgrading from VC7.1 to VC8 using Visual studio .net 2005 IDE. I am successfully able to Upgrade the project but when I am rebuilding the solution, I am getting some wired error.It says that "Template Cannot be Declared to have C Linkage" This is urgent and I have no Clue. Please provide an appropriate solution. Thanks in advance. -Abhay Try this http://www.nabble.com/error-C2894:-templates-cannot-be-declared-to-have- 'C'-linkage-t3341636.html (Watch wrap) > -----Original Message----- > From: abhayparashar01@gmail.com [mailto:abhayparashar01@gmail.com]...

2 cells 1 formula
1st, Thanks for the help.... I have this simple formula if(a1=" ",a1,if(a1=>1,5)) I put this formula in cell c3 and it works, if a1 is blank then c3 turns blank, and if cell a1 is 1 or more c3 turns 5. Now I want to do the same adding cell b1 and a1 leaving this formula in cell c3, like this, if cells a1 & b1 are blank then c1 turns blank. and if either a1 or b1 has a value say #1 then c1 turns 5. Every formula I write if a1 or b1 is blank and the other has a number I get a VALUE error which I understand, but can't figure how to write the formula. ...

Criteria/CountIf and Pivot Table
Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 $0.00 2 08/13/2006 1 $10.00 3 08/14/2006 1 $20.00 4 08/01/2006 0 $0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

Custom letter 1-at-a-time?
In Outlook 2000, our office would like to select 1 contact and have info feed into a custom letter format (with our margins, indents, etc.) DOES ANYONE KNOW A WAY TO ACHIEVE THIS CUSTOM FORMAT LETTER FOR 1 CONTACT AT THE "PUSH OF A BUTTON"? Have found some coding on the web dating from 1997, but suspect changes in Outlook since then cause need to troubleshoot these solutions. We're not that savvy. Thank you, Nia ...

Problems receiving email from specific domains
We host our own ex2k3 server internally. A few weeks ago we switched to a new ISP at work. Everything went very smoothly overall. Emails seemed to be sending and receiving without much issue. However, we were intermittently receiving emails from a small handful of domains. We could send emails to those domains but most emails from those domains were not being received. A restart of our ex2k3 server cured the problem for everyone except emails being received from gmail and sbcglobal. Even then some emails would make it through but most don't. Any idea why this is happening? Did you check ...

http/1.1 503 Service unavailable
Hey, We are getting this error when people try to access OWA, i have seen some KB articles about this but nothing that really describes how to fix it other than the davex.dll which i have also seen you should not do because it mess things up even more so..... i am wondering if any of you have had this error and fixed it some other way, this is an exchange 2003 server (single) thanks ...

Countif
I am trying to solve the following problem i) cells A1 to A10 have either M or F (male or female) ii) cells B1 to B10 have either (grades) A B or C iii) I want (eg cell B13) to state the number of female students who scored A... and cell B14; how many female students who gained B etc I have tried various things including countif, sumif etc. It seems like a straight forward problem but the solution evades me! Eddie =SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1))) in Cell B13 and filled down to Cell B15. Alan Beban Eddie wrote: > I am trying to solve ...

What are the options for getting a test copy of 1.2?
I have CRM 1.0, ActionPack, and Solution Provider with MSDN Universal, but I am not at this point a Business Partner. What routes are open to obtain a test copy of 1.2 and what are the expected time frames for availability to existing 1.0 users, Action Pack subscribers, and MSDN download Subscribers? Bill Walter Bill If you go to the MSDN site they normally tell you what will be shipping next. Cant remember the exact link but it is definately there "Bill Walter" <wqwalter@hotmail.com> wrote in message news:uEya515sDHA.2508@TK2MSFTNGP12.phx.gbl... > I have CRM 1.0, Ac...

Upgrade to 1.2 Documentation
I am looking for some documentation that states start to finish the upgrade procedure for upgrading CRM 1.0 to 1.2. Refer to the implementation guided included with CRM 1.2 The exact steps involved in any upgrade will vary depending on your deployment. Mike "DMJ" <kmd@hotmail> wrote in message news:%23MAKA3E4DHA.2136@TK2MSFTNGP12.phx.gbl... > I am looking for some documentation that states start to finish the upgrade > procedure for upgrading CRM 1.0 to 1.2. > > > > As mike said, it should be in the new IG (Installation Guide, pdf format), that com...

HTTP/1.1 401 Unauthorized error in OWA
Has anyone else experienced an HTTP/1.1 401 Unauthorized error when trying to access OWA in Exchange 2000? I have users who attempt to login 3 times and then get that error. I'm wondering if this is a result of some changes I made to recipient policies? Per MS Article: 319759 Any help is greatly appreciated. Thanks, Jeff Solved this one: The Exchange Virtual Directory in IIS was not pointing to the proper location. Changing my recipient policies a week ago seems to have exacerbated an existing problem which caused the error page. I went into IIS and modified the path that the vir...

Find next record according to specific criteria
I have a field on a form that has a checkbox. I want to make a button that will go to the next record that doesn't have the checkbox checked (I don't want to filter the records, just to advance to the next unchecked record). What code do I use to accomplish this? You can use the FindNext method to do that: With Me.RecordsetClone .FindNext "[CheckBoxFieldName] = 0" If .NoMatch Then MsgBox "No More UnChecked Records" Else Me.Bookmark = .Bookmark End If End With Note CheckBoxFieldName is the name of the...

Reports Receipts on New 1.3 install Disk
Are all the Reports and Receipts on the 1.3 version the newer ones that are in customer source or are the all the orignal ones that were on the 1.2 disk. I just want to make sure the are the latest so that all I have to do is move over my custom stuff. Fabino, A couple of them have been changed, but I don't remember which ones and I haven't found a KB as to which ones they are. Look in CustomerSource http://tinyurl.com/c987v and find the fixed reports/receipts/PO's etc and they are on the disk. If you have customized they are using the same name, back them up first, beca...

5.7.1 smtp;554 5.7.1 User Over Quota
One of my users receive this message when she attempts to send any e-mails to one particular users. Does anyone have a clue about this message. i can't find any information on this site or any other about this error message. Do you have a sending quota set? To check on a user account navigate to (aduc > users account > properties >Exchange General > Storage limits). If use mailbox default is selected, a mailbox policy is applied "fanman36" wrote: > One of my users receive this message when she attempts to send any e-mails > to one particular users. Do...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1$3371a4f0$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

CountIf with And
Novice Excel user on Excel 2003. I have a yearly data input sheet to track codes. I use the date (fomatted as 1/01/2009) and a code. I want to track the codes in a mothly chart for ease in summarization and improvment tracking. The codes are used to define a department and an error in that department (code 1a means - AR department and error a). I need to pull and total the amount for each code for each month. After searching for a while it seems that using CountIf with And does not work. Any suggestions would be greatly appreciated. -- Mike =SUMPRODUCT(--(MONTH(date_range)=4),...