Counting items in one column based on another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
(e-mail address removed)

----- (e-mail address removed) 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
Click to expand...
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,
Click to expand...
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.
 
Sam
Your welcome, and thanks for the enthusiastic response. It's also nice to see a name instead of (e-mail address removed)

Regards
Mark Graesse
(e-mail address removed)

----- Sam Mitchell wrote: ----

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 Mitchel
CPT, S
2nd REC BD
ASST S3-Trainin
-----Original Message----
anonymous
The COUNTIF function does not allow more than one
Click to expand...
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 outand 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
 
Back
Top