| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf and CountIf Multiple Criteria
Help Please! I need to learn how to get the average of two columns excluding
certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf and CountIf Multiple Criteria
Try:
=SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA")*(U1 1:U1143=0)*(V11:V1143=0)*U11:V1143)/MAX(1,SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA" )*(U11:U1143=0)*(V11:V1143=0))) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:54 xdemechanik --- "Rose" wrote: Help Please! I need to learn how to get the average of two columns excluding certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf and CountIf Multiple Criteria
Thanks so much for your quick reply. I copied and pasted to my worksheet but
got the dreaded #VALUE! Any suggestions, I really appreciate your help. Many thanks, Rose "Max" wrote: Try: =SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA")*(U1 1:U1143=0)*(V11:V1143=0)*U11:V1143)/MAX(1,SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA" )*(U11:U1143=0)*(V11:V1143=0))) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:54 xdemechanik --- "Rose" wrote: Help Please! I need to learn how to get the average of two columns excluding certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf and CountIf Multiple Criteria
One possible way
=SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0)*(U11 :V1143<"")*(U11:V1143))/MAX(1,SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0) *(U11:V1143<""))) -- Regards, Peo Sjoblom "Rose" wrote in message ... Help Please! I need to learn how to get the average of two columns excluding certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf and CountIf Multiple Criteria
Hi Peo,
Thanks for your quick response, I copied and pasted your suggestion into my worksheet but got the dreaded #VALUE! error. Any other suggestions please? Many thanks for your time, Rose "Peo Sjoblom" wrote: One possible way =SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0)*(U11 :V1143<"")*(U11:V1143))/MAX(1,SUMPRODUCT((B11:B1143<"PTA")*(U11:V1143=0) *(U11:V1143<""))) -- Regards, Peo Sjoblom "Rose" wrote in message ... Help Please! I need to learn how to get the average of two columns excluding certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf and CountIf Multiple Criteria
The reason you get a value error is that you have text in U11:V1143, you
should always avoid that as much as possible. Btw, why are you using =0 in your original formula, can there be negative numbers? -- Regards, Peo Sjoblom "Rose" wrote in message ... Thanks so much for your quick reply. I copied and pasted to my worksheet but got the dreaded #VALUE! Any suggestions, I really appreciate your help. Many thanks, Rose "Max" wrote: Try: =SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA")*(U1 1:U1143=0)*(V11:V1143=0)*U11:V1143)/MAX(1,SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA" )*(U11:U1143=0)*(V11:V1143=0))) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:54 xdemechanik --- "Rose" wrote: Help Please! I need to learn how to get the average of two columns excluding certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf and CountIf Multiple Criteria
Thank you so much, you saved my life - this is for work. There was text in
the columns in particular the word "blank" the worksheet is a pivot table and sometimes when I refresh the data the pivot table will insert the word blank. I deselected blank from those columns and it worked. As you can probably already tell I am self taught and a beginner in Excel. To answer your question about why are you using =0 in your original formula, can there be negative numbers? I'm not sure as this came from my boss, we were having trouble getting the average formula to work in the first place because the columns had empty cells so she found this formula somewhere and had me enter it. Now that we excluded the PTA survey's from that cell she wants only the PTA survey average in the cell next to it . What would that formula look like please? Again many thanks for your help! Rose "Peo Sjoblom" wrote: The reason you get a value error is that you have text in U11:V1143, you should always avoid that as much as possible. Btw, why are you using =0 in your original formula, can there be negative numbers? -- Regards, Peo Sjoblom "Rose" wrote in message ... Thanks so much for your quick reply. I copied and pasted to my worksheet but got the dreaded #VALUE! Any suggestions, I really appreciate your help. Many thanks, Rose "Max" wrote: Try: =SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA")*(U1 1:U1143=0)*(V11:V1143=0)*U11:V1143)/MAX(1,SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA" )*(U11:U1143=0)*(V11:V1143=0))) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:54 xdemechanik --- "Rose" wrote: Help Please! I need to learn how to get the average of two columns excluding certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf and CountIf Multiple Criteria
If Max's formula worked then just change this part in all occurrences
B11:B1143<"PTA" to this B11:B1143="PTA" -- Regards, Peo Sjoblom "Rose" wrote in message ... Thank you so much, you saved my life - this is for work. There was text in the columns in particular the word "blank" the worksheet is a pivot table and sometimes when I refresh the data the pivot table will insert the word blank. I deselected blank from those columns and it worked. As you can probably already tell I am self taught and a beginner in Excel. To answer your question about why are you using =0 in your original formula, can there be negative numbers? I'm not sure as this came from my boss, we were having trouble getting the average formula to work in the first place because the columns had empty cells so she found this formula somewhere and had me enter it. Now that we excluded the PTA survey's from that cell she wants only the PTA survey average in the cell next to it . What would that formula look like please? Again many thanks for your help! Rose "Peo Sjoblom" wrote: The reason you get a value error is that you have text in U11:V1143, you should always avoid that as much as possible. Btw, why are you using =0 in your original formula, can there be negative numbers? -- Regards, Peo Sjoblom "Rose" wrote in message ... Thanks so much for your quick reply. I copied and pasted to my worksheet but got the dreaded #VALUE! Any suggestions, I really appreciate your help. Many thanks, Rose "Max" wrote: Try: =SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA")*(U1 1:U1143=0)*(V11:V1143=0)*U11:V1143)/MAX(1,SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA" )*(U11:U1143=0)*(V11:V1143=0))) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:54 xdemechanik --- "Rose" wrote: Help Please! I need to learn how to get the average of two columns excluding certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf and CountIf Multiple Criteria
I spoke too soon I forgot that by deselecting the "blanks" from the pivot
table it takes all the data out that had the blanks so my numbers are wrong. Is there a way to fix your formula to include the cells that have the word blank or text in it? Thanks so much for your help, Rose "Peo Sjoblom" wrote: If Max's formula worked then just change this part in all occurrences B11:B1143<"PTA" to this B11:B1143="PTA" -- Regards, Peo Sjoblom "Rose" wrote in message ... Thank you so much, you saved my life - this is for work. There was text in the columns in particular the word "blank" the worksheet is a pivot table and sometimes when I refresh the data the pivot table will insert the word blank. I deselected blank from those columns and it worked. As you can probably already tell I am self taught and a beginner in Excel. To answer your question about why are you using =0 in your original formula, can there be negative numbers? I'm not sure as this came from my boss, we were having trouble getting the average formula to work in the first place because the columns had empty cells so she found this formula somewhere and had me enter it. Now that we excluded the PTA survey's from that cell she wants only the PTA survey average in the cell next to it . What would that formula look like please? Again many thanks for your help! Rose "Peo Sjoblom" wrote: The reason you get a value error is that you have text in U11:V1143, you should always avoid that as much as possible. Btw, why are you using =0 in your original formula, can there be negative numbers? -- Regards, Peo Sjoblom "Rose" wrote in message ... Thanks so much for your quick reply. I copied and pasted to my worksheet but got the dreaded #VALUE! Any suggestions, I really appreciate your help. Many thanks, Rose "Max" wrote: Try: =SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA")*(U1 1:U1143=0)*(V11:V1143=0)*U11:V1143)/MAX(1,SUMPRODUCT((B11:B1143<"")*(B11:B1143<"PTA" )*(U11:U1143=0)*(V11:V1143=0))) -- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:54 xdemechanik --- "Rose" wrote: Help Please! I need to learn how to get the average of two columns excluding certain types which are in a third column. Column U is Survey Score column V is Additional Survey Score but I need to exclude the type of "PTA" survey scores which are in column B. The current formula we use just to get the average is of the two columns is: =SUMIF(U11:V1143,"=0")/MAX(1,COUNTIF(U11:V1143,"=0")) What do I need to add to the formula to exclude the "PTA" scores, the score types are in column B. Or do I need to use a totally different formula to achieve this? Best Regards, Rose |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CountIF() & SumIF() with multiple criteria | Excel Worksheet Functions | |||
| Countif or sumif with 2 criteria | Excel Discussion (Misc queries) | |||
| Countif & Sumif with Multiple criteria | Excel Worksheet Functions | |||
| multiple criteria with countif or sumif | Excel Worksheet Functions | |||
| Multiple Criteria for COUNTIF and SUMIF | Excel Worksheet Functions | |||
Linear Mode
