Count in 1 column based on another column

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

babs

Board Regular
Joined
Oct 30, 2002
Messages
106
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..
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
Joined
Jun 10, 2002
Messages
139
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))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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... :biggrin:
 

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top