Multiple arguments

[Tim S]

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...

 

[Ron Rosenfeld]

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")

 

[Tim S]

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.

 

[Ron Rosenfeld]

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.