Countif with 1+ specifics

[Bob Phillips]

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

 

[Ken Wright]

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

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

 

[Ken Wright]

rotflmao - Had forgotten it was Ian that started the thread - Glad you reminded
me

 

[Ken Wright]

Doh - missed the .com bit - (e-mail address removed)

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

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

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

 

[Harlan Grove]

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.

 

[Frank Kabel]

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