# Counting Multiple Values in a column based on critera in seperate column

1. ## Counting Multiple Values in a column based on critera in seperate column

Hello!

I have a list of 100+ values that will be listed in column A. I would like to count how many times each value occurs in Column A given that Column F does not contain a value.

I have previously been using the formula =COUNTIFS(A:A, "VALUE NAME", F:F, ""), and that does the job, however, to type that formula for the 100+ values individually is very time consuming.

Additionally, the columns I am counting from are on sheet1 and the list of values that will be used in column A are on a separate worksheet (sheet2) in the same workbook.

I am hoping that there is a simpler way to do it, maybe a macro?

I have attached the workbook for your viewing pleasure, there are other issues I am also working through, so just ignore anything that doesn't seem right.

2. ## Re: Counting Multiple Values in a column based on critera in seperate column

extract the unique values onto the 2nd sheet using

In Cell Sheet 2 A2 put

=INDEX(sheet1!\$A\$2:\$A\$100, MATCH(0, COUNTIF(\$A1:A\$1, sheet1!\$A\$2:\$A\$100), 0))
and enter as an array formula using
control + shift + enter so you get {} around the formula

and then you can use
in sheet 2 B2

=Countif(=COUNTIF(sheet1!A2:A100,sheet2!A2)

dont understand the example sheet you posted as on sheet no data in column A
and the cells names in well names appear unique ??

you could also use a pivot table if needed

3. ## Re: Counting Multiple Values in a column based on critera in seperate column

ERoberts,

Welcome to the forum!
Why not just reference the cell containing the "VALUE NAME" in the formula instead of typing it out? So instead of:
``Please Login or Register  to view this content.``

Use this:
``Please Login or Register  to view this content.``

4. ## Re: Counting Multiple Values in a column based on critera in seperate column

Thanks Etaf, I can see how that would work, however to make sure that rows where column F has value, the formula would have to be slightly altered.

Tigeravatar, I tried it and it works like a gem.

Thank ya'll so much!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1