Countif with 1+ specifics

[ianripping]

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

 

[Harlan Grove]

=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."}))

 

[Ken Wright]

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

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

 

[ianripping]

Excellent. Thanks guys!

 

[ianripping]

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"}))

 

[Bob Phillips]

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)

 

[Frank Kabel]

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

Ian,

The formula works fine for me. Check the data.

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"}
))

 

[Bob Phillips]

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]

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

 

[Ken Wright]

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??

 

[Frank Kabel]

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

 

[Bob Phillips]

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)

 

[Ken Wright]

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

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

 

[Bob Phillips]

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)

 

[Ken Wright]

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

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

 

[Bob Phillips]

Exactly the same conclusion I came to, but weird or what?

Bob

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]

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

 

[Ken Wright]

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

Exactly the same conclusion I came to, but weird or what?

Bob

<snip>

 

[Frank Kabel]

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]

Yeah, we've had fun, but Ian still has his problem?

Bob