how to count#cells w/= value in other column and not count blank c

's Computer Specifications

In column A, I have values equal to either YES, NO or NA. In column B, I
have values equal to either YES, NO or NA. I want to count the # of cells in
column B that equal to their counterpart in column A, but I do not want to
count blank cells, ex:
A B
1 Yes Yes
2 No No
3 NA No
4
5 Yes Yes
6 Yes Yes
7
8 No No
9 No No
Result for B10 should be 6 as I do not want to count row 4 and row 7

thanks in advance!

's Computer Specifications

=SUMPRODUCT(--(A1:A9=B1:B9);--(NOT(ISBLANK(A1:A9))))

--
Regards!
Stefi

â€žaganoeâ€ ezt Ã*rta:

> In column A, I have values equal to either YES, NO or NA. In column B, I
> have values equal to either YES, NO or NA. I want to count the # of cells in
> column B that equal to their counterpart in column A, but I do not want to
> count blank cells, ex:
> A B
> 1 Yes Yes
> 2 No No
> 3 NA No
> 4
> 5 Yes Yes
> 6 Yes Yes
> 7
> 8 No No
> 9 No No
> Result for B10 should be 6 as I do not want to count row 4 and row 7
>
> thanks in advance!

's Computer Specifications

Try the below

=SUMPRODUCT(--(A1:A9=B1:B9)*(A1:A9<>""))

--
Jacob (MVP - Excel)

"aganoe" wrote:

> In column A, I have values equal to either YES, NO or NA. In column B, I
> have values equal to either YES, NO or NA. I want to count the # of cells in
> column B that equal to their counterpart in column A, but I do not want to
> count blank cells, ex:
> A B
> 1 Yes Yes
> 2 No No
> 3 NA No
> 4
> 5 Yes Yes
> 6 Yes Yes
> 7
> 8 No No
> 9 No No
> Result for B10 should be 6 as I do not want to count row 4 and row 7
>
> thanks in advance!

's Computer Specifications

Thanks, all three suggestions work!

"Stefi" wrote:

> =SUMPRODUCT(--(A1:A9=B1:B9);--(NOT(ISBLANK(A1:A9))))
>
>
> --
> Regards!
> Stefi
>
>
>
> â€žaganoeâ€ ezt Ã*rta:
>
> > In column A, I have values equal to either YES, NO or NA. In column B, I
> > have values equal to either YES, NO or NA. I want to count the # of cells in
> > column B that equal to their counterpart in column A, but I do not want to
> > count blank cells, ex:
> > A B
> > 1 Yes Yes
> > 2 No No
> > 3 NA No
> > 4
> > 5 Yes Yes
> > 6 Yes Yes
> > 7
> > 8 No No
> > 9 No No
> > Result for B10 should be 6 as I do not want to count row 4 and row 7
> >
> > thanks in advance!

's Computer Specifications

You are welcome! Thanks for the feedback!

Clicking the YES button will be appreciated.

--
Regards!
Stefi

â€žaganoeâ€ ezt Ã*rta:

> Thanks, all three suggestions work!
>
> "Stefi" wrote:
>
> > =SUMPRODUCT(--(A1:A9=B1:B9);--(NOT(ISBLANK(A1:A9))))
> >
> >
> > --
> > Regards!
> > Stefi
> >
> >
> >
> > â€žaganoeâ€ ezt Ã*rta:
> >
> > > In column A, I have values equal to either YES, NO or NA. In column B, I
> > > have values equal to either YES, NO or NA. I want to count the # of cells in
> > > column B that equal to their counterpart in column A, but I do not want to
> > > count blank cells, ex:
> > > A B
> > > 1 Yes Yes
> > > 2 No No
> > > 3 NA No
> > > 4
> > > 5 Yes Yes
> > > 6 Yes Yes
> > > 7
> > > 8 No No
> > > 9 No No
> > > Result for B10 should be 6 as I do not want to count row 4 and row 7
> > >
> > > thanks in advance!

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
count the number of cells which are blank in a excel column	shantanu	Microsoft C# .NET	2	5th Apr 2007 04:27 AM
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN	=?Utf-8?B?Y2Fycmlja2E=?=	Microsoft Excel Worksheet Functions	1	6th May 2005 04:50 PM
How do I count blank cells in a column of dates?	Ron	Microsoft Excel Programming	4	8th Apr 2005 06:46 PM
count non blank cells which meet criteria in another column	=?Utf-8?B?Y21hcnNoNTAzNQ==?=	Microsoft Excel Worksheet Functions	2	16th Feb 2005 04:32 PM
Count non-blank cells in a table column	Bradley C. Hammerstrom	Microsoft Word New Users	4	14th Dec 2004 01:47 AM