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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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..
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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
Back
Top