ExcelBanter - Getting a Count

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Getting a Count (https://www.excelbanter.com/excel-worksheet-functions/196061-getting-count.html)

I have two columns, one of which has "2000" among other text while the other
has "No." I need to count the number of times there is "No" in a row if it
also contains "2000." From reading previous posts, if appeared that the
following would work, but it doesn't. It just brings the calculation to 0.
What is the proper of way accomplishing this? Thanks.

=SUMPRODUCT((B18:B351="*2000*")*(C18:C351="No"))

=SUMPRODUCT(--(ISNUMBER(FIND("2000",B18:B351))),--(C18:C351="No"))

"John Deakins" wrote:

I have two columns, one of which has "2000" among other text while the other
has "No." I need to count the number of times there is "No" in a row if it
also contains "2000." From reading previous posts, if appeared that the
following would work, but it doesn't. It just brings the calculation to 0.
What is the proper of way accomplishing this? Thanks.

=SUMPRODUCT((B18:B351="*2000*")*(C18:C351="No"))

I've got a similar situation.
a b c
1 42 7
2 #N/A 7
3 33 8
4 #N/A 8
5 96 8

I need to count the number of cells where a number appears, but not '#N/A',
when column C is 8. So for above, Ans: count =2
Could anyone help ?

"Teethless mama" wrote:

=SUMPRODUCT(--(ISNUMBER(FIND("2000",B18:B351))),--(C18:C351="No"))

"John Deakins" wrote:

I have two columns, one of which has "2000" among other text while the other
has "No." I need to count the number of times there is "No" in a row if it
also contains "2000." From reading previous posts, if appeared that the
following would work, but it doesn't. It just brings the calculation to 0.
What is the proper of way accomplishing this? Thanks.

=SUMPRODUCT((B18:B351="*2000*")*(C18:C351="No"))