Calculating rows with "a,b,c" data? Possible?

  • Thread starter Thread starter Mark Livingstone
  • Start date Start date
M

Mark Livingstone

Hi,

My COLUMN A is:

a
a
b
c,d
a,b
d
a
c

I can calculate all As or Ds but it won't calculate "c,d" or "a,b". Is
there a way I can either calculate these multiple items individually
and group them together with the liek items (i.e. c,d would be counted
as "c" and "d") or count them as a separate category and display the
results? (the second method is preferred).

And a final question. I have a formula that references another sheet
(i.e. Region1!E2:E449,"z"). How would I go about substituting the
sheet's name (Region) with a cell that contains that sheet's name? So,
instead of writing everywhere Region1, I would just specify C90, that
way I can easily get data from various sheets without having to change
every single formula.

Thanks.
 
...
...
My COLUMN A is:

a
a
b
c,d
a,b
d
a
c

I can calculate all As or Ds but it won't calculate "c,d" or "a,b". Is
there a way I can either calculate these multiple items individually
and group them together with the liek items (i.e. c,d would be counted
as "c" and "d") or count them as a separate category and display the
results? (the second method is preferred).
Click to expand...

=COUNTIF(YourRangeHere,"*"&ParticularLetterHere&"*")

E.g., if your list above were in A1:A8, and A10 contained a then the formula

=COUNTIF(A1:A8,"*"&A10&"*")

would return 4.
And a final question. I have a formula that references another sheet
(i.e. Region1!E2:E449,"z"). How would I go about substituting the
sheet's name (Region) with a cell that contains that sheet's name? So,
instead of writing everywhere Region1, I would just specify C90, that
way I can easily get data from various sheets without having to change
every single formula.
Click to expand...

If cell X99 contained the worksheet name, then use something like

=COUNTIF(INDIRECT("'"&X99&"'!"&MID(CELL("Address",(A1,E2:E449)),6,256)),"*z*")
 
Harlan Grove said:
Why are you posting in HTML?
Click to expand...

i post from Google Groups.. so whatever format it posts, it posts :)
why? your newsgroup client does not support HTML?
 
Hi Mark!

The groups do support HTML but it "costs" more in terms of size and
time of downloads for which people have to pay; you wouldn't dream of
asking someone to pay to give you directions so...

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
The groups do support HTML

Well, not really - "the groups" support text messages. It's up to the
newsreader to interpret it. Some newsreaders support HTML, some don't.

Many of us (especially those who have been reading news as long as I
have), choose to read and post only in plain text. In addition to the
excellent reason you give, HTML is not archived in Google; the HTML is
usually badly formed and does not render well for those with visual
impairments; it potentially carries destructive payloads, depending on
how the newsreader handles it. It's also seems rather arrogant, in a
forum based on information exchange, to send a message that forces style
preferences on the reader.

The biggest reason not to post in HTML, though, is that it limits the
audience. My newsreader kills any HTML post, sight unseen, and plonks
the poster for future posts. That may be unusual, but I'm sure I'm not
alone.

(And FWIW, my email client automatically trashes any HTML email, too.
It's an abomination, the spawn of the devil...)
 
Hi JE!

Re: "It's an abomination, the spawn of the devil"

So can I take it you don't like HTML?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
The biggest reason not to post in HTML, though, is that it limits the
audience. My newsreader kills any HTML post, sight unseen, and plonks
the poster for future posts. That may be unusual, but I'm sure I'm not
alone.
Click to expand...

You're not!
(And FWIW, my email client automatically trashes any HTML email, too.
It's an abomination, the spawn of the devil...)
Click to expand...

Though I do not delete it it comes directly in the 'potential Spam'
folder

Frank
 
No, I love HTML! - I write all my web pages using it (well, XHTML 1.1
Strict, but...)

I just have a teensy feeling of hate and loathing for it in email and
news postings, that's all.
 
i post from Google Groups.. so whatever format it posts, it posts :)
why? your newsgroup client does not support HTML?
Click to expand...

I wasn't responding to you, but to Bob Umlas. Postings from Google Groups are in
plain text because Google has the good sense to make them so. Postings from
Microsoft's CDO newsgroup interface are in HTML/MIMEd. Since I haven't been
really cynical in a few days, I suppose this is part of a long term plot by M$FT
to charge us for the priviledge of providing customer support for them. That, or
they just don't give a damn about the dangers inherent in overly broad HTML
support. Given virus/worm stats over the last few years, I suppose the latter is
much more likely.
 
Back
Top