Countifs bites again

[Please work this time...]

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,"<=500")

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

 

[Claus Busch]

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,"<=500")

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",Sheet3!AQ8:AQ107,"<=500",Sheet3!AR8:AR107,"<=500")


Regards
Claus Busch

 

[Living the Dream]

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.

 

[Please work this time...]

Hi,



I have a spreadsheet with 4 different criteria and all of them have to betrue 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,"<=500")



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



So any help is accepted.



thanks a lot



Hi,



I have a spreadsheet with 4 different criteria and all of them have to betrue 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,"<=500")



The formula evulates correctly untill it hits Sheet3!AP8:AR107 values areless 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.

 

[Please work this time...]

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",Sheet3!AQ8:AQ107,"<=500",Sheet3!AR8:AR107,"<=500")

thanks again