Strange request: Counting cells and percentages
 

I'm not even sure how to formulate this right, so please forgive the generic
thread title.

I'm racking my brains trying to come up with a formula for an analytical
spreadsheet I'm working on. What I need to do, and haven't been able to
figure it out, is to count how many occurrences o X I have, excluding some
cells, and represent that in a percentage of the total.

To give a clearer picture, I have many columns with years. Say, 2008, 2007,
2006, and so on. Each year has, say, either X or Y. Now I can figure out the
times X occurs, and its percentage of total, no problem. But what I can't see
how to formulate, is getting the total and percentage of total since a given
year. For instance, if my spreadsheet has years going back to 1980, I want to
get a percentage of the total since 1995 only.

Am I being clear? It makes sense to me...

Strange request: Counting cells and percentages
 

Let's assume:

B1:H1 = year numbers 2008,2007,2006 ...2002
B2:H10 = X or Y

To count X from 2008 to a chosen year:

A15 = chosen year

=SUMPRODUCT(--(B2:B10:INDEX(B2:H10,,MATCH(A15,B1:H1,0))="X"))

From that, you should be able to figure out how to get the %.

--
Biff
Microsoft Excel MVP


"jmj713" wrote in message
...
I'm not even sure how to formulate this right, so please forgive the
generic
thread title.

I'm racking my brains trying to come up with a formula for an analytical
spreadsheet I'm working on. What I need to do, and haven't been able to
figure it out, is to count how many occurrences o X I have, excluding some
cells, and represent that in a percentage of the total.

To give a clearer picture, I have many columns with years. Say, 2008,
2007,
2006, and so on. Each year has, say, either X or Y. Now I can figure out
the
times X occurs, and its percentage of total, no problem. But what I can't
see
how to formulate, is getting the total and percentage of total since a
given
year. For instance, if my spreadsheet has years going back to 1980, I want
to
get a percentage of the total since 1995 only.

Am I being clear? It makes sense to me...

Strange request: Counting cells and percentages
 

Thanks for this formula, but I think I'm either implementing it wrong, or it
doesn't fit what I'm looking for.

What I need to have are several things: Total (from 1980 to 2008), Total X
(number and percent of total), Total Y (number and percent of total), and for
each X and Y also Total from 1995 to 2008, and this number also represent as
percent of total since 1995. Does that make sense? This should give me seven
numbers. And the "total percent of X" and the "total percent of X since 1995"
should theoretically be different numbers.

"T. Valko" wrote:

Let's assume:

B1:H1 = year numbers 2008,2007,2006 ...2002
B2:H10 = X or Y

To count X from 2008 to a chosen year:

A15 = chosen year

=SUMPRODUCT(--(B2:B10:INDEX(B2:H10,,MATCH(A15,B1:H1,0))="X"))

From that, you should be able to figure out how to get the %.

--
Biff
Microsoft Excel MVP


"jmj713" wrote in message
...
I'm not even sure how to formulate this right, so please forgive the
generic
thread title.

I'm racking my brains trying to come up with a formula for an analytical
spreadsheet I'm working on. What I need to do, and haven't been able to
figure it out, is to count how many occurrences o X I have, excluding some
cells, and represent that in a percentage of the total.

To give a clearer picture, I have many columns with years. Say, 2008,
2007,
2006, and so on. Each year has, say, either X or Y. Now I can figure out
the
times X occurs, and its percentage of total, no problem. But what I can't
see
how to formulate, is getting the total and percentage of total since a
given
year. For instance, if my spreadsheet has years going back to 1980, I want
to
get a percentage of the total since 1995 only.

Am I being clear? It makes sense to me...

Strange request: Counting cells and percentages
 

That is to say, I can figure out how to get a total of X since 1995. I can't
figure out how to get a relative percentage of X since 1995.

Strange request: Counting cells and percentages
 

I see you started another post so I'm deferring to that other post.

--
Biff
Microsoft Excel MVP


"jmj713" wrote in message
...
Thanks for this formula, but I think I'm either implementing it wrong, or
it
doesn't fit what I'm looking for.

What I need to have are several things: Total (from 1980 to 2008), Total X
(number and percent of total), Total Y (number and percent of total), and
for
each X and Y also Total from 1995 to 2008, and this number also represent
as
percent of total since 1995. Does that make sense? This should give me
seven
numbers. And the "total percent of X" and the "total percent of X since
1995"
should theoretically be different numbers.

"T. Valko" wrote:

Let's assume:

B1:H1 = year numbers 2008,2007,2006 ...2002
B2:H10 = X or Y

To count X from 2008 to a chosen year:

A15 = chosen year

=SUMPRODUCT(--(B2:B10:INDEX(B2:H10,,MATCH(A15,B1:H1,0))="X"))

From that, you should be able to figure out how to get the %.

--
Biff
Microsoft Excel MVP


"jmj713" wrote in message
...
I'm not even sure how to formulate this right, so please forgive the
generic
thread title.

I'm racking my brains trying to come up with a formula for an
analytical
spreadsheet I'm working on. What I need to do, and haven't been able to
figure it out, is to count how many occurrences o X I have, excluding
some
cells, and represent that in a percentage of the total.

To give a clearer picture, I have many columns with years. Say, 2008,
2007,
2006, and so on. Each year has, say, either X or Y. Now I can figure
out
the
times X occurs, and its percentage of total, no problem. But what I
can't
see
how to formulate, is getting the total and percentage of total since a
given
year. For instance, if my spreadsheet has years going back to 1980, I
want
to
get a percentage of the total since 1995 only.

Am I being clear? It makes sense to me...