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

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

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.

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.

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.