Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default how to count#cells w/= value in other column and not count blank c

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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default how to count#cells w/= value in other column and not count blank c

=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!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default how to count#cells w/= value in other column and not count blank c

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!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default how to count#cells w/= value in other column and not count bla

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!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default how to count#cells w/= value in other column and not count bla

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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Non-Blank Cells in Column If a Reference Criteria Met. [email protected] Excel Discussion (Misc queries) 5 August 23rd 09 03:05 AM
Only count columns if the column next to it is not blank glotgering Excel Discussion (Misc queries) 2 March 30th 06 09:51 PM
Count Non Blank in column Diane Alsing Excel Discussion (Misc queries) 3 September 16th 05 03:12 AM
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN carricka Excel Worksheet Functions 1 May 6th 05 04:50 PM
count non blank cells which meet criteria in another column cmarsh5035 Excel Worksheet Functions 2 February 16th 05 05:32 PM


All times are GMT +1. The time now is 02:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"