I need help with the thread below."Countifs bites again"

https://groups.google.com/forum/?fro...ns/rDHn9VjHDlQ

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. So if eg ap8 is 450 and ar8 is 350 then the function needs to return 2 and so on.


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

Hi,

Am Tue, 26 Mar 2013 10:25:11 -0700 (PDT) schrieb Please work this
time...:

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. So if eg ap8 is 450 and ar8 is 350 then the function needs to return 2 and so on.

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

the formula above counts only if e.g. AP8 /AND/ AQ8 /AND/ AR8 are <=500
For your expected result you have to add:
=COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*", Sheet3!AO8:AO107,"*",Sheet3!AP8:AP107,"<=500")+
COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*",S heet3!AO8:AO107,"*",Sheet3!AQ8:AQ107,"<=500")+
COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*",S heet3!AO8:AO107,"*",Sheet3!AR8:AR107,"<=500")

or try it with SUMPRODUCT:
=SUMPRODUCT(((AP8:AP107<=500)+(AQ8:AQ107<=500)+(AR 8:AR107<=500))*(C8:C107<"")*(AH8:AH107<"")*(AO8: AO107<""))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thank for the prompt answer. I used your SUMPRODUCT formula above i have2 values that are lesst than 500 in AP8:AP107 & AR8:AR107 $350.00 & $450.00 but still the sumporduct is evulating as a 0. Can I use some other function?


On Tuesday, March 26, 2013 12:41:10 PM UTC-5, Claus Busch wrote:
Hi,



Am Tue, 26 Mar 2013 10:25:11 -0700 (PDT) schrieb Please work this

time...:



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. So if eg ap8 is 450 and ar8 is 350 then the function needs to return 2 and so on.



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



the formula above counts only if e.g. AP8 /AND/ AQ8 /AND/ AR8 are <=500

For your expected result you have to add:

=COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*", Sheet3!AO8:AO107,"*",Sheet3!AP8:AP107,"<=500")+

COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*",S heet3!AO8:AO107,"*",Sheet3!AQ8:AQ107,"<=500")+

COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*",S heet3!AO8:AO107,"*",Sheet3!AR8:AR107,"<=500")



or try it with SUMPRODUCT:

=SUMPRODUCT(((AP8:AP107<=500)+(AQ8:AQ107<=500)+(AR 8:AR107<=500))*(C8:C107<"")*(AH8:AH107<"")*(AO8: AO107<""))





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Hi,

Am Tue, 26 Mar 2013 11:02:32 -0700 (PDT) schrieb Please work this
time...:

Thank for the prompt answer. I used your SUMPRODUCT formula above i have2 values that are lesst than 500 in AP8:AP107 & AR8:AR107 $350.00 & $450.00 but still the sumporduct is evulating as a 0. Can I use some other function?

these values only will be counted if the cell in C, AH /and/ AO in the
same row are not empty.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi,

Am Tue, 26 Mar 2013 19:07:31 +0100 schrieb Claus Busch:

these values only will be counted if the cell in C, AH /and/ AO in the
same row are not empty.

if you want to count the values in AP8:AR107 <= 500 without any other
condition, then try:
=COUNTIF(AP8:AR107,"<=500")


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Ok When i insert the following statement and restrict it to just one cell I get the correct answer which is 2. I am looking for "r" in these cells.
so this formula works fine but only for one cell at a time.

=SUMPRODUCT((Sheet3!AP8:AP8<=500)+(Sheet3!AQ8:AQ8< =500)+(Sheet3!AR8:AR8<=500))*(Sheet3!C8:C8<"*")*( Sheet3!AH8:AH8<"*")*(Sheet3!AL8:AL8<"*")

But when I try to expand my range by using the following formula I get a result of 299, I should only get 2 as only C8 & Ah8 & al8 have a value of "r" in ity and the rest of the rows are blank have empty cells in them.

=SUMPRODUCT((Sheet3!AP8:AP107<=500)+(Sheet3!AQ8:AQ 107<=500)+(Sheet3!AR8:AR107<=500))*(Sheet3!C8:C107 <"*")*(Sheet3!AH8:AH107<"*")*(Sheet3!AL8:AL107< "*")

Can you throw some light on it please. I do appreciate all your help.
On Tuesday, March 26, 2013 1:39:11 PM UTC-5, Claus Busch wrote:
Hi,



Am Tue, 26 Mar 2013 19:07:31 +0100 schrieb Claus Busch:



these values only will be counted if the cell in C, AH /and/ AO in the

same row are not empty.



if you want to count the values in AP8:AR107 <= 500 without any other

condition, then try:

=COUNTIF(AP8:AR107,"<=500")





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2