Erase cell contents based upon a condition

  • Thread starter Thread starter skiing
  • Start date Start date
S

skiing

I have a huge list which counts the number of items in the list (per
each record) in the total row. Based upon an entered condition ( like
put a 2 in B1 and compare all entries down that column to the 2 - if
the entry in each cell equals the 2 - great - if not, erase the 2 and
leave the cell blank). I use the CountA at the end of the row, for
each record, to see how many items this person has versus another
record.

A B C D
2 Total
Jim 2 4 2
Bob 1 3 1

Jim's total remains at 2 because he matches the entry in B1, Bob's
total is 1 because he does not match the entry in B1. I would like to
have Bob's entry in column B to be erased - so I can use the counta
function to get the total in column D. Currently I am
"looking" (manual) down the column and erasing the entry which does
not match - I have approx 250 records and 390 columns to deal with

thank you for any assistance you can offer.
 
It seems as if you want to delete some values just so that you can get
COUNTA to work on your data. There are other count functions, however,
so perhaps you should look in XL Help to find what you can do with
COUNTIF (or COUNTIFS if you have XL2007). SUMPRODUCT is another
function which might be relevant, as you seem to have multiple
conditions, but it is not very clear to me what you are starting out
with and what you ultimately want to achieve.

Hope this helps.

Pete
 
It seems as if you want to delete some values just so that you can get
COUNTA to work on your data. There are other count functions, however,
so perhaps you should look in XL Help to find what you can do with
COUNTIF (or COUNTIFS if you have XL2007). SUMPRODUCT is another
function which might be relevant, as you seem to have multiple
conditions, but it is not very clear to me what you are starting out
with and what you ultimately want to achieve.

Hope this helps.

Pete






- Show quoted text -
Click to expand...

I have tried many of the functions - as every column is it's own
condition - any function I would use would have approx 350 criteria
items . Below is example data

Value 2 4 5 8 9 12

name Test 1 Test 2 Test 3 Test 4 Test 5 Test 6 Test 7
Matches (thru test 6)
Bill 4 5 9 13 3
jim 2 8 14 2

Where a person's row matches the value row - I want to count those
items. Bill matches for Test 2, 3, and 9 - thus total of matches =
3, Jim matches for test 1 and 4 - thus total of matches = 2.
Currently I scroll down test 1's column, remove (erase) all the items
not equal to the entry in the value row for test 1 and my =counta
function will work as it counts the cells with data in them. I
would like to have an automatic feature in place so when I enter Test
7's value at the top - the Test 7 column of entries would retain the
entry if it matches value of Test 7 at the top but would erase the
entry if it did not match the value of Test 7 at the top. I would
then modify the formula in "Matches" column to include the column for
Test 7.

hope this helps
 
Back
Top