# Count in 1 column based on another column

#### PocketNeo

##### Board Regular
SHEET 1:
COL A : has test names (Test1, test2, etc.)
Where COL A can repeat a test (test1, test1, test2, test3, test1, test2, test2, etc.)

COL B : has Pass or Fail.

SHEET 2:
In COL A I have a list of all unique tests (test1, test2, etc.)
In COL B I'd like a count of unique tests from sheet1 col a.
In COL B "PASS" I'd like a count of pass for each test.
In COL C "FAIL" I'd like a count of fail for each test

I tried using arrays, countif for the COLA AND COLB of sheet2, but I'm stumped.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### babs

##### Board Regular
Use Vlookup to find the Value of Test and Countif formula to count the number of Pass or Fail.

have to rush but can write in detail for both the functions.. Dig in Help..

##### MrExcel MVP
On 2002-10-31 15:16, PocketNeo wrote:
SHEET 1:
COL A : has test names (Test1, test2, etc.)
Where COL A can repeat a test (test1, test1, test2, test3, test1, test2, test2, etc.)

COL B : has Pass or Fail.

SHEET 2:
In COL A I have a list of all unique tests (test1, test2, etc.)
In COL B I'd like a count of unique tests from sheet1 col a.
In COL B "PASS" I'd like a count of pass for each test.
In COL C "FAIL" I'd like a count of fail for each test

I tried using arrays, countif for the COLA AND COLB of sheet2, but I'm stumped.

Sheet2...

Let A2 house the first test name, test1, and so on...

In B1 enter: Total Count

In C1 enter: PASS

In D1 enter: FAIL

In B2 enter & copy down for all tests:

=COUNTIF(Sheet1!\$A\$2:\$A\$300,A2)

In C2 enter & copy across then down:

=SUMPRODUCT((Sheet1!\$A\$2:\$A\$300=\$A2)*(Sheet1!\$B\$2:\$B\$300=B\$1))

#### PocketNeo

##### Board Regular
Didn't work. I checked your formula and it should have been the following. (I modified the C\$1)

In Sheet 2, C2:
=SUMPRODUCT((Sheet1!\$A\$2:\$A\$300=\$A2)*(Sheet1!\$B\$2:\$B\$300=C\$1))

##### MrExcel MVP

On 2002-10-31 16:01, PocketNeo wrote:
Didn't work. I checked your formula and it should have been the following. (I modified the C\$1)

In Sheet 2, C2:
=SUMPRODUCT((Sheet1!\$A\$2:\$A\$300=\$A2)*(Sheet1!\$B\$2:\$B\$300=C\$1))

Yes, it should have been C\$1, but that's just a typo...

#### PocketNeo

##### Board Regular
Yes...thanks again...I appreciate it.
I tried learning about SUMPRODUCT and can't seem to understand the use of the * in your formula. Can you explain?

Thanks.

##### MrExcel MVP
On 2002-11-01 16:21, PocketNeo wrote:
Yes...thanks again...I appreciate it.
I tried learning about SUMPRODUCT and can't seem to understand the use of the * in your formula. Can you explain?

Thanks.

The * is the algebriac version of the logical connective AND... The link below treats SumProduct in more detail:

http://www.mrexcel.com/wwwboard/messages/8961.html
This message was edited by Aladin Akyurek on 2002-11-02 14:52

#### PocketNeo

##### Board Regular
Very informative.

I really enjoyed this and will definately re-use it.

Replies
3
Views
102
Replies
2
Views
143
Replies
1
Views
286
Replies
2
Views
427
Replies
6
Views
97

1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back