Mick & Clause & Experts Please help me on this issue one more time

[Please work this time...]

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

https://groups.google.com/forum/?fr....public.excel.worksheet.functions/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",Sheet3!AQ8:AQ107,"<=500",Sheet3!AR8:AR107,"<=500")

 

[Claus Busch]

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",Sheet3!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,"*",Sheet3!AO8:AO107,"*",Sheet3!AQ8:AQ107,"<=500")+
COUNTIFS(Sheet3!C8:C107,"*",Sheet3!AH8:AH107,"*",Sheet3!AO8:AO107,"*",Sheet3!AR8:AR107,"<=500")

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


Regards
Claus Busch

 

[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?

 

[Claus Busch]

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

 

[Claus Busch]

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

 

[Please work this time...]

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:AQ107<=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.

 

[Claus Busch]

Hi,

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

=SUMPRODUCT((Sheet3!AP8:AP107<=500)+(Sheet3!AQ8:AQ107<=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:AQ107<=500)+(Sheet3!AR8:AR107<=500))*(Sheet3!C8:C107="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

 

[Please work this time...]

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

 

[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.

 

[Claus Busch]

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:AQ107<=500)*(AQ8:AQ107)+(AR8:AR107<=500)*(AR8:AR107))*(C8:C107&AH8:AH107&AO8:AO107="rrr"))


Regards
Claus Busch

 

[Please work this time...]

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

 

[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.
I tried the following but it did not work
=SUMPRODUCT(((Sheet3!AP8:AP107>0<=500)+(Sheet3!AQ8:AQ107>0<=500)+(Sheet3!AR8:AR107>0<=500))*(Sheet3!C8:C107="r")*(Sheet3!AH8:AH107="r")*(Sheet3!AL8:AL107="r"))

thanks again for your help

 

[Claus Busch]

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:AP107>0)+(AQ8:AQ107<=500)*(AQ8:AQ107>0)+(AR8:AR107<=500)*(AR8:AR107>0))*(C8:C107&AH8:AH107&AO8:AO107="rrr"))


Regards
Claus Busch

 

[Please work this time...]

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