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

Hi,

Am Tue, 26 Mar 2013 12:08:46 -0700 (PDT) schrieb Please work this
time...:

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

in the formula above I miss a bracket:
=SUMPRODUCT(((Sheet3!AP8:AP107<=500)+(Sheet3!AQ8:A Q107<=500)+(Sheet3!AR8:AR107<=500))*(Sheet3!C8:C10 7="r")*(Sheet3!AH8:AH107="r")*(Sheet3!AL8:AL107="r "))
In each row must the conditions be true.
If this formula gives not the expected result you must change your
conditions.


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

Thanks that worked great . I am sorry if I had to get your attention. I do really appreciates all the help. Its hard to trouble shoot these problems. Excel is not very helpfull on guiding where the issue is.

thanks


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



Am Tue, 26 Mar 2013 12:08:46 -0700 (PDT) schrieb Please work this

time...:



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



in the formula above I miss a bracket:

=SUMPRODUCT(((Sheet3!AP8:AP107<=500)+(Sheet3!AQ8:A Q107<=500)+(Sheet3!AR8:AR107<=500))*(Sheet3!C8:C10 7="r")*(Sheet3!AH8:AH107="r")*(Sheet3!AL8:AL107="r "))

In each row must the conditions be true.

If this formula gives not the expected result you must change your

conditions.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

I have one more question what if in the above issue. I wanted to sum the dollar amounts instead of counting them so I have 2 amounts that 300.00 & 400.00 and I want to sum them getting the result of 700.00.
Could you guide me on this second issue please.

On Tuesday, March 26, 2013 3:34:44 PM UTC-5, Please work this time... wrote:
Thanks that worked great . I am sorry if I had to get your attention. I do really appreciates all the help. Its hard to trouble shoot these problems. Excel is not very helpfull on guiding where the issue is.



thanks





On Tuesday, March 26, 2013 2:16:41 PM UTC-5, Claus Busch wrote:

Hi,







Am Tue, 26 Mar 2013 12:08:46 -0700 (PDT) schrieb Please work this



time...:







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







in the formula above I miss a bracket:



=SUMPRODUCT(((Sheet3!AP8:AP107<=500)+(Sheet3!AQ8:A Q107<=500)+(Sheet3!AR8:AR107<=500))*(Sheet3!C8:C10 7="r")*(Sheet3!AH8:AH107="r")*(Sheet3!AL8:AL107="r "))



In each row must the conditions be true.



If this formula gives not the expected result you must change your



conditions.











Regards



Claus Busch



--



Win XP PRof SP2 / Vista Ultimate SP2



Office 2003 SP2 /2007 Ultimate SP2

Hi,

Am Tue, 26 Mar 2013 13:41:12 -0700 (PDT) schrieb Please work this
time...:

I have one more question what if in the above issue. I wanted to sum the dollar amounts instead of counting them so I have 2 amounts that 300.00 & 400.00 and I want to sum them getting the result of 700.00.
Could you guide me on this second issue please.

try:
=SUMPRODUCT(((AP8:AP107<=500)*(AP8:AP107)+(AQ8:AQ1 07<=500)*(AQ8:AQ107)+(AR8:AR107<=500)*(AR8:AR107)) *(C8:C107&AH8:AH107&AO8:AO107="rrr"))


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

Tried your last post i am getting the correct answer . Thanks agin for your help. Have a great day.


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



Am Tue, 26 Mar 2013 13:41:12 -0700 (PDT) schrieb Please work this

time...:



I have one more question what if in the above issue. I wanted to sum the dollar amounts instead of counting them so I have 2 amounts that 300.00 & 400.00 and I want to sum them getting the result of 700.00.

Could you guide me on this second issue please.



try:

=SUMPRODUCT(((AP8:AP107<=500)*(AP8:AP107)+(AQ8:AQ1 07<=500)*(AQ8:AQ107)+(AR8:AR107<=500)*(AR8:AR107)) *(C8:C107&AH8:AH107&AO8:AO107="rrr"))





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

One more issue just popped up using the formula below I get the correct answer when there are no zeros in the cells AP8:AP107 & Aq8:Aq107 & AR8:AR107 but when there is a zero in the above ranges the formula counts the zeros also . I only want to count greater than zero but less than 500.
I tried the following but it did not work
=SUMPRODUCT(((Sheet3!AP8:AP1070<=500)+(Sheet3!AQ8 :AQ1070<=500)+(Sheet3!AR8:AR1070<=500))*(Sheet3! C8:C107="r")*(Sheet3!AH8:AH107="r")*(Sheet3!AL8:AL 107="r"))

thanks again for your help

On Tuesday, March 26, 2013 4:46:21 PM UTC-5, Please work this time... wrote:
Tried your last post i am getting the correct answer . Thanks agin for your help. Have a great day.





On Tuesday, March 26, 2013 4:10:26 PM UTC-5, Claus Busch wrote:

Hi,







Am Tue, 26 Mar 2013 13:41:12 -0700 (PDT) schrieb Please work this



time...:







I have one more question what if in the above issue. I wanted to sum the dollar amounts instead of counting them so I have 2 amounts that 300.00 & 400.00 and I want to sum them getting the result of 700.00.



Could you guide me on this second issue please.







try:



=SUMPRODUCT(((AP8:AP107<=500)*(AP8:AP107)+(AQ8:AQ1 07<=500)*(AQ8:AQ107)+(AR8:AR107<=500)*(AR8:AR107)) *(C8:C107&AH8:AH107&AO8:AO107="rrr"))











Regards



Claus Busch



--



Win XP PRof SP2 / Vista Ultimate SP2



Office 2003 SP2 /2007 Ultimate SP2

Hi,

Am Tue, 26 Mar 2013 15:31:03 -0700 (PDT) schrieb Please work this
time...:

One more issue just popped up using the formula below I get the correct answer when there are no zeros in the cells AP8:AP107 & Aq8:Aq107 & AR8:AR107 but when there is a zero in the above ranges the formula counts the zeros also . I only want to count greater than zero but less than 500.

try:
=SUMPRODUCT(((AP8:AP107<=500)*(AP8:AP1070)+(AQ8:A Q107<=500)*(AQ8:AQ1070)+(AR8:AR107<=500)*(AR8:AR1 070))*(C8:C107&AH8:AH107&AO8:AO107="rrr"))


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

Thank you again its working. I am getting the correct numbers now.Have a great day.

On Tuesday, March 26, 2013 5:40:31 PM UTC-5, Claus Busch wrote:
Hi,



Am Tue, 26 Mar 2013 15:31:03 -0700 (PDT) schrieb Please work this

time...:



One more issue just popped up using the formula below I get the correct answer when there are no zeros in the cells AP8:AP107 & Aq8:Aq107 & AR8:AR107 but when there is a zero in the above ranges the formula counts the zeros also . I only want to count greater than zero but less than 500.



try:

=SUMPRODUCT(((AP8:AP107<=500)*(AP8:AP1070)+(AQ8:A Q107<=500)*(AQ8:AQ1070)+(AR8:AR107<=500)*(AR8:AR1 070))*(C8:C107&AH8:AH107&AO8:AO107="rrr"))





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2