Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Countifs bites again

Hi,

I have a spreadsheet with 4 different criteria and all of them have to be true in order for me to get the count I desire. In the formula below the first 3 critera evaluate fine but then the formula does not evulate the last criteria. Here is the formula:

I want to get a count if all the criteria are correct ie if there is something in Sheet3!C8:C107 & Sheet3!AH8:AH107 & Sheet3!AO8:AO107 & Sheet3!AP8:AR107 values are less than or equal to $500.00.



=COUNTIFS(Sheet3!C8:C107,"=?",Sheet3!AH8:AH107,"=? ",Sheet3!AO8:AO107,"=?",Sheet3!AP8:AR107,"<=50 0")

The formula evulates correctly untill it hits Sheet3!AP8:AR107 values are less than or equal to $500.00. then I get an error.

So any help is accepted.

thanks a lot
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Countifs bites again

Hi,

Am Fri, 22 Mar 2013 15:32:56 -0700 (PDT) schrieb Please work this
time...:

I have a spreadsheet with 4 different criteria and all of them have to be true in order for me to get the count I desire. In the formula below the first 3 critera evaluate fine but then the formula does not evulate the last criteria. Here is the formula:

I want to get a count if all the criteria are correct ie if there is something in Sheet3!C8:C107 & Sheet3!AH8:AH107 & Sheet3!AO8:AO107 & Sheet3!AP8:AR107 values are less than or equal to $500.00.

=COUNTIFS(Sheet3!C8:C107,"=?",Sheet3!AH8:AH107,"=? ",Sheet3!AO8:AO107,"=?",Sheet3!AP8:AR107,"<=50 0")


please look into help for this function:
"Importantly each additional range must the same number of rows and
columns as the argument criteria range1"
So you have to split AP8:AR107 to three columns:
=COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*", Sheet3!AO8:AO107,"*",Sheet3!AP8:AP107,"<=500",Shee t3!AQ8:AQ107,"<=500",Sheet3!AR8:AR107,"<=500")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 151
Default Countifs bites again


Hi

As Claus has pointed out regarding your last bracket, you will need to
split the 2 columns off.

You could also use SUMPRODUCT rather than multiple CountIF's as earlier
versions of excel do not have this feature as they are restricted to
only 3 IF Statements if I recall.

=SUMPRODUCT(--(Sheet3!C8:C107="?"),--(Sheet3!AH8:AH107="?"),--(Sheet3!AO8:AO107="?"),--(Sheet3!AP8:AP107="?"),--(Sheet3!AR8:AR107<=500))

HTH
Mick.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Countifs bites again

On Friday, March 22, 2013 5:32:56 PM UTC-5, Please work this time... wrote:
Hi,



I have a spreadsheet with 4 different criteria and all of them have to be true in order for me to get the count I desire. In the formula below the first 3 critera evaluate fine but then the formula does not evulate the last criteria. Here is the formula:



I want to get a count if all the criteria are correct ie if there is something in Sheet3!C8:C107 & Sheet3!AH8:AH107 & Sheet3!AO8:AO107 & Sheet3!AP8:AR107 values are less than or equal to $500.00.







=COUNTIFS(Sheet3!C8:C107,"=?",Sheet3!AH8:AH107,"=? ",Sheet3!AO8:AO107,"=?",Sheet3!AP8:AR107,"<=50 0")



The formula evulates correctly untill it hits Sheet3!AP8:AR107 values are less than or equal to $500.00. then I get an error.



So any help is accepted.



thanks a lot




On Friday, March 22, 2013 5:32:56 PM UTC-5, Please work this time... wrote:
Hi,



I have a spreadsheet with 4 different criteria and all of them have to be true in order for me to get the count I desire. In the formula below the first 3 critera evaluate fine but then the formula does not evulate the last criteria. Here is the formula:



I want to get a count if all the criteria are correct ie if there is something in Sheet3!C8:C107 & Sheet3!AH8:AH107 & Sheet3!AO8:AO107 & Sheet3!AP8:AR107 values are less than or equal to $500.00.







=COUNTIFS(Sheet3!C8:C107,"=?",Sheet3!AH8:AH107,"=? ",Sheet3!AO8:AO107,"=?",Sheet3!AP8:AR107,"<=50 0")



The formula evulates correctly untill it hits Sheet3!AP8:AR107 values are less than or equal to $500.00. then I get an error. I will try the SUMPRODUCT also,



So any help is accepted.



thanks a lot


Thank you guys , I really appreciate the help I will post back if I have a issue with the guidance you guys provided.
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default Countifs bites again

Back again with the countif.

I am using the following now, but i realised an error in my logic.
What needs to happen is that if the first 3 conditions are correct then I need to return the count of of all the columns AP8:AP107,AQ8:AQ107 & AR8:AR107 that
have a value <=500.

=COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*", Sheet3!AO8:AO107,"*",Sheet3!AP8:AP107,"<=500",Shee t3!AQ8:AQ107,"<=500",Sheet3!AR8:AR107,"<=500")

thanks again



On Saturday, March 23, 2013 4:20:12 PM UTC-5, Please work this time... wrote:
On Friday, March 22, 2013 5:32:56 PM UTC-5, Please work this time... wrote:

Hi,








I have a spreadsheet with 4 different criteria and all of them have to be true in order for me to get the count I desire. In the formula below the first 3 critera evaluate fine but then the formula does not evulate the last criteria. Here is the formula:








I want to get a count if all the criteria are correct ie if there is something in Sheet3!C8:C107 & Sheet3!AH8:AH107 & Sheet3!AO8:AO107 & Sheet3!AP8:AR107 values are less than or equal to $500.00.
















=COUNTIFS(Sheet3!C8:C107,"=?",Sheet3!AH8:AH107,"=? ",Sheet3!AO8:AO107,"=?",Sheet3!AP8:AR107,"<=50 0")








The formula evulates correctly untill it hits Sheet3!AP8:AR107 values are less than or equal to $500.00. then I get an error.








So any help is accepted.








thanks a lot








On Friday, March 22, 2013 5:32:56 PM UTC-5, Please work this time... wrote:

Hi,








I have a spreadsheet with 4 different criteria and all of them have to be true in order for me to get the count I desire. In the formula below the first 3 critera evaluate fine but then the formula does not evulate the last criteria. Here is the formula:








I want to get a count if all the criteria are correct ie if there is something in Sheet3!C8:C107 & Sheet3!AH8:AH107 & Sheet3!AO8:AO107 & Sheet3!AP8:AR107 values are less than or equal to $500.00.
















=COUNTIFS(Sheet3!C8:C107,"=?",Sheet3!AH8:AH107,"=? ",Sheet3!AO8:AO107,"=?",Sheet3!AP8:AR107,"<=50 0")








The formula evulates correctly untill it hits Sheet3!AP8:AR107 values are less than or equal to $500.00. then I get an error. I will try the SUMPRODUCT also,








So any help is accepted.








thanks a lot




Thank you guys , I really appreciate the help I will post back if I have a issue with the guidance you guys provided.

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
countifs gus New Users to Excel 2 November 18th 09 06:16 AM
Countifs Carl Excel Worksheet Functions 1 April 22nd 09 04:28 PM
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr Steve Excel Worksheet Functions 2 January 4th 09 06:36 PM
Countifs Fx in 07 how in 03? HenderH Excel Discussion (Misc queries) 19 March 26th 08 01:37 PM
0 bites Mark From Perth Excel Discussion (Misc queries) 4 July 6th 06 12:59 PM


All times are GMT +1. The time now is 02:39 AM.

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"