Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Multiple arguments

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...
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default Multiple arguments

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Multiple arguments

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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default Multiple arguments

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.
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
Multiple arguments Nobrainer22 Excel Worksheet Functions 1 October 31st 09 05:03 PM
If, multiple arguments puiuluipui Excel Discussion (Misc queries) 4 April 6th 09 02:23 PM
IF multiple arguments [email protected] Excel Worksheet Functions 2 March 18th 08 10:19 PM
Multiple Arguments ijt Excel Discussion (Misc queries) 3 November 20th 06 07:48 AM
Multiple Arguments dansargeant Excel Worksheet Functions 3 March 10th 06 09:06 AM


All times are GMT +1. The time now is 01:06 PM.

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"