Hi - first posting in here. I have a range ab862:ab891 which has one of the following text strings entered into the cell range:
no data
no day shift data
no night shift data

I need to be able to count how many cells have any of these statements in the cell range. Ive tried countifs, sumproduct from other posts but im not making it work somehow...

=COUNTIF(AB862:AB891,"no data") works fine with any of the statements one at a time
=COUNTIFS(AB862:AB891,"no data", "no day shift data") returns the help dialog box
=SUMPRODUCT(--(AB862:AB891="no data"), --(AB862:AB891="no night shift data")) returns no error and doesnt return a result other than zero
=COUNTIFS(AB862:AB891,"no data",AB862:AB891,"no night shift data") doesnt return any errors, but it doesnt return a result other than zero either...

Please help I'm stumped...

On Wed, 22 Jan 2014 22:15:24 -0800 (PST), Tim S wrote:

Hi - first posting in here. I have a range ab862:ab891 which has one of the following text strings entered into the cell range:
no data
no day shift data
no night shift data

I need to be able to count how many cells have any of these statements in the cell range. Ive tried countifs, sumproduct from other posts but im not making it work somehow...

=COUNTIF(AB862:AB891,"no data") works fine with any of the statements one at a time
=COUNTIFS(AB862:AB891,"no data", "no day shift data") returns the help dialog box
=SUMPRODUCT(--(AB862:AB891="no data"), --(AB862:AB891="no night shift data")) returns no error and doesnt return a result other than zero
=COUNTIFS(AB862:AB891,"no data",AB862:AB891,"no night shift data") doesnt return any errors, but it doesnt return a result other than zero either...

Please help I'm stumped...

A simple formula that will do what I think you want might be:

=COUNTIF(AB862:AB891,"no data")+COUNTIF(AB862:AB891,"no day shift data")+COUNTIF(AB862:AB891,"no night shift data")

For compactness, you can combine the variables into an array constant, and then sum them as in:

=sum(countif(ab862:ab891,{"no data","no day shift data","no night shift data"}))

Depending on what else might be in those cells, you might even be able to use a wild card setup:

=countif(ab862:ab891,"no* data")

On Thursday, January 23, 2014 9:20:40 PM UTC+10, Ron Rosenfeld wrote:
On Wed, 22 Jan 2014 22:15:24 -0800 (PST), Tim S wrote:



Hi - first posting in here. I have a range ab862:ab891 which has one of the following text strings entered into the cell range:

no data

no day shift data

no night shift data



I need to be able to count how many cells have any of these statements in the cell range. Ive tried countifs, sumproduct from other posts but im not making it work somehow...



=COUNTIF(AB862:AB891,"no data") works fine with any of the statements one at a time

=COUNTIFS(AB862:AB891,"no data", "no day shift data") returns the help dialog box

=SUMPRODUCT(--(AB862:AB891="no data"), --(AB862:AB891="no night shift data")) returns no error and doesnt return a result other than zero

=COUNTIFS(AB862:AB891,"no data",AB862:AB891,"no night shift data") doesnt return any errors, but it doesnt return a result other than zero either...



Please help I'm stumped...



A simple formula that will do what I think you want might be:



=COUNTIF(AB862:AB891,"no data")+COUNTIF(AB862:AB891,"no day shift data")+COUNTIF(AB862:AB891,"no night shift data")



For compactness, you can combine the variables into an array constant, and then sum them as in:



=sum(countif(ab862:ab891,{"no data","no day shift data","no night shift data"}))



Depending on what else might be in those cells, you might even be able to use a wild card setup:



=countif(ab862:ab891,"no* data")

Sweet as Ron - I used the wildcard option and it worked like a charm... I'll be pasting these into my common formula bank to save me from having to filter through my posts here, lol. Love your work.

On Mon, 27 Jan 2014 22:58:41 -0800 (PST), Tim S wrote:

Sweet as Ron - I used the wildcard option and it worked like a charm... I'll be pasting these into my common formula bank to save me from having to filter through my posts here, lol. Love your work.

Glad to help. Thanks for the feedback.