Calculating rows with "a,b,c" type of 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.

P.S. You guys are great at answering! Thanks for the previous help.
 
Hi
for your first question: Depens what you currently calculate. If you
use for example COUNTIF you may try
=COUNTIF(A1:A100,"c*")

For the second question use
=INDIRECT("'" & A1 & "'!E2:E449")
where A1 stores your sheetname
 
this will count all with c, before or after.
=COUNTIF(A1:A3,"*c*")

try using indirect
example=INDIRECT(B8&"!c3")
 
Back
Top