Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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") |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more 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?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more 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: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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more 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 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more 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. 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more 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: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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mick & Clause & Experts Please help me on this issue one more time
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time issue | Excel Programming | |||
Can I use a between clause or in clause on an IF statement | Excel Programming | |||
Excel format JOHN MICK to Text format "JOHN","MICK" | Excel Discussion (Misc queries) | |||
"Between" in an IF clause | Excel Discussion (Misc queries) | |||
Run Time Issue | Excel Programming |