Counting items in one column based on another

Hello,

Working on a spreadsheet which will take information from
a daily variable .xls down load, check the information,
and then return values based on the results of the first
colum of analysis.

Setup:

First column contains text string such
as "3A1A", "3A2B", "3D1A", etc. Second column contains
another text string such as "GMA", "GFA", "PSA", etc.

What I want to do:

BLUF: Return a count from two columns that meets the
criteria in both columns.

The first step is to check a text string in the first
column for a given result "3A*" and then give a count
result for all entries that meet "3A*" and "G*A" result.

The problem is that I can not seem to nest a COUNTIF
function that will meet the first criteria of selecting
only the rows that contain "3A*" before it counts
the "G*A" in the second column.

I have tried about everything I can think of plus looking
at the HELP file on Excel.

anonymous,
The COUNTIF function does not allow more than one criterion. You can get what you need using SUMPRODUCT. Another problem is you can't use the wildcard * inside of the quotes. I pu together the following which should help you out:

=SUMPRODUCT((LEFT(A1:A10,2)="3A")*(LEFT(B1:B10,1)& MID(B1:B10,3,1)="GA"))

This will check the first two left character in column A, and then check the 1st and 3rd characters in column B. The SUMPRODUCT will create two arrays of 0 and 1 (false and true) for each condition. It then multiplies the corresponding terms, and adds up these products.

example:
(1,1,1,0,0,0) * (1,0,1,0,1,0) = (1,0,1,0,0,0) then (1+0+1+0+0+0) = 2

Good Luck,
Mark Graesser


----- wrote: -----

Hello,

Working on a spreadsheet which will take information from
a daily variable .xls down load, check the information,
and then return values based on the results of the first
colum of analysis.

Setup:

First column contains text string such
as "3A1A", "3A2B", "3D1A", etc. Second column contains
another text string such as "GMA", "GFA", "PSA", etc.

What I want to do:

BLUF: Return a count from two columns that meets the
criteria in both columns.

The first step is to check a text string in the first
column for a given result "3A*" and then give a count
result for all entries that meet "3A*" and "G*A" result.

The problem is that I can not seem to nest a COUNTIF
function that will meet the first criteria of selecting
only the rows that contain "3A*" before it counts
the "G*A" in the second column.

I have tried about everything I can think of plus looking
at the HELP file on Excel.

Thanks Mark,

IT FREAKING WORKED! Thanks alot for helping think outside
the box on this thing! IT will make some folks jobs alot
easier!

Sam Mitchell
CPT, SC
2nd REC BDE
ASST S3-Training

-----Original Message-----
anonymous,
The COUNTIF function does not allow more than one
criterion. You can get what you need using SUMPRODUCT.
Another problem is you can't use the wildcard * inside of
the quotes. I pu together the following which should help
you out:

=SUMPRODUCT((LEFT(A1:A10,2)="3A")*(LEFT(B1:B10,1) &MID
(B1:B10,3,1)="GA"))

This will check the first two left character in column A,
and then check the 1st and 3rd characters in column B.
The SUMPRODUCT will create two arrays of 0 and 1 (false
and true) for each condition. It then multiplies the
corresponding terms, and adds up these products.

example:
(1,1,1,0,0,0) * (1,0,1,0,1,0) = (1,0,1,0,0,0) then
(1+0+1+0+0+0) = 2

Good Luck,
Mark Graesser


----- wrote: ----
-

Hello,

Working on a spreadsheet which will take information
from
a daily variable .xls down load, check the
information,
and then return values based on the results of the
first
colum of analysis.

Setup:

First column contains text string such
as "3A1A", "3A2B", "3D1A", etc. Second column
contains
another text string such as "GMA", "GFA", "PSA", etc.

What I want to do:

BLUF: Return a count from two columns that meets
the
criteria in both columns.

The first step is to check a text string in the
first
column for a given result "3A*" and then give a
count
result for all entries that meet "3A*" and "G*A"
result.

The problem is that I can not seem to nest a COUNTIF
function that will meet the first criteria of
selecting
only the rows that contain "3A*" before it counts
the "G*A" in the second column.

I have tried about everything I can think of plus
looking
at the HELP file on Excel.

.