function to count items in two columns

  • Thread starter Thread starter Mickie Dietz-Eaton
  • Start date Start date
M

Mickie Dietz-Eaton

Hi. I've searched the boards here and have found several answers to
questions I found difficult. Thanks for any help in advance.

I have two questions that I need help with at this time.

First:

I have two columns with test (Pre = L and Post = O) scores and want to
count only those records which contain scores in EITHER column at or
above 250. I tried the following...but I realize I'm doing something
wrong. I haven't worked with VBA or macros before.

=COUNTIF(OR(L2:L263,">=250",O2:O263,">=250"))

Second:

I am somewhat familiar with the Autofilter and Pivot Table
features...but could use a little help here too.

I want to perform calculations (MODE, MEDIAN, etc.) on much of the
data in this same database...but sort by two other columns possibly at
the same time. I found a list of SUBTOTAL formulas 1-11 on this
site...but was unable to find ones for MODE and MEDIAN. Am I limited
to these 11 formulas?

Here's a simplified version of what I want to do.

Column Headings:
Pre Scores
Post Scores
Group 1
Group 2
Group 3
Group 4

I want to run reports on the following:

Compare Pre/Post Scores on those individuals who participated in both
Group A and B.

Compare Pre/Post Scores on those individuals who partipipated in Group
A but NOT Group B. etc.

Maybe Pivot Tables are the best way to report on this
information...but I'm curious if formulas exist that provide the data
for analysis in another format.

Thanks for any help you can give.
 
...
...
First:

I have two columns with test (Pre = L and Post = O) scores and want to
count only those records which contain scores in EITHER column at or
above 250. I tried the following...but I realize I'm doing something
wrong. I haven't worked with VBA or macros before.

=COUNTIF(OR(L2:L263,">=250",O2:O263,">=250"))
Try

=SUMPRODUCT(--((L2:L263>=250)+(O2:O263>=250)>0))


...
I want to perform calculations (MODE, MEDIAN, etc.) on much of the
data in this same database...but sort by two other columns possibly at
the same time. I found a list of SUBTOTAL formulas 1-11 on this
site...but was unable to find ones for MODE and MEDIAN. Am I limited
to these 11 formulas?
Click to expand...

Unfortunately, SUBTOTAL is limited to these 11 subfunctions. I doesn't calculate
group medians or modes.
 
Harlan,

Thanks again for the tip. I tried your method...and thought it worked
at first. I think I left out a small detail of importance. I only
want to count records in one column at or above 250 where the record
in the other column is blank. I have students who pre-tested, but may
not have taken a post-test if scoring in that range on the pre.


Harlan Grove wrote...
Try

=SUMPRODUCT(--((L2:L263>=250)+(O2:O263>=250)>0))
Click to expand...


Thanks again.
 
Back
Top