ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   how to count#cells w/= value in other column and not count blank c (https://www.excelbanter.com/excel-worksheet-functions/260973-how-count-cells-w-%3D-value-other-column-not-count-blank-c.html)

aganoe

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!

Stefi

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!


Jacob Skaria

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!


aganoe

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!


Stefi

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!



All times are GMT +1. The time now is 11:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com