# 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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### 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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,656
Messages
5,838,625
Members
430,557
Latest member
MK15

### 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