Compare and count 2 columns

**pictogram** · 01-29-2010, 12:51 PM

Hi,
I'm desperately looking for THE formula.

Here's the context :
---------A----------B--------
1------CR------Marie
2------CR------
3------CR------Alex
4------CR------Martin
5------CR------

In another sheet of my file, I want to calculate how many persons (col B) have the CR code (col A).
I must specify that there's many other different codes (col A) and that sometimes there's no person specified for a code (see my example). In my example, the answer i'm looking for is 3.
I tried with IF, AND, OR, ...without success.

Thanks for any help.

**NBVC** · 01-29-2010, 01:05 PM

Try

=Sumproduct(--(Sheet1!A1:A100="CR"),--(Sheet1!B1:B100<>""))

adjust ranges and sheetnmame to suit

**Richard Buttrey** · 01-29-2010, 01:10 PM

Hi,

One way using a helper column C

Please Login or Register  to view this content.

then use

Please Login or Register  to view this content.

You could use a

Please Login or Register  to view this content.

formula, which may be better if you only have a few rows otherwise since it's an array formula it will take longer to calculate.

HTH

**ChemistB** · 01-29-2010, 01:13 PM

NBVC's solution will work if names are not repeated. I had trouble doing it without a helper or dummy column. In the dummy column, I used this formula dragged down(column C)

Please Login or Register  to view this content.

which returns "True" whenever a name appears for the first time. Then this formula works

Please Login or Register  to view this content.

Will that work for you?

**pictogram** · 01-29-2010, 03:10 PM

Thanks, I think that would be it !!! But Excel won't accept this formula ?? The error message : "The formula you entered contains an error ...". Here's my formula :
=Sumproduct(--('Jan 2010'!F3:F100="CR"),--('Jan 2010'!I3:I100<>"")). The comma between the 2 arrays seems to be the problem !!?!? I tried with other symbols ( ; * / ). No error message as above, but they won't let excel return anything good (normal u will say !!).

Thanks

**ChemistB** · 01-29-2010, 03:21 PM

Your version of Excel probably uses semi colon instead of comma. When you say it doesn't return anything normal, what does it return (when using semicolon)? Upload a dummy workbook if you want us to troubleshoot it.

**pictogram** · 01-29-2010, 04:02 PM

Thanks ChemistB,

Here you go with a dummy file. 2 sheets in it : Jan 2010 with some data and Stats for the results.
Semi-colon returns a $NAME? error