| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF based on several criteria.
OK here is what I want to do. I have three different values that I want to
sum. I have several criteria that I have to consider when I sum them. A B C D Sum Total 89 90 105 100 100 110 75 0 85 75 65 0 Go 65 75 0 No/Go OK so here is a sample set of values. I want to sum the values from left to right. For the majority of my totals I will only need to do a straight sum(A,B,C) no big deal. However from time to time as in the first example where run into a qualification. Problem 1. If any of the three values are under 90 and any of the other values are over 100 I need to round the value over 100 down to 100. Problem 2. If any of the the values are a 0 then I need to be able in the same cell average the other 2 number and then add the three values together. I have tried to use nested If statments, looked at SUMIFS and SUMIF but the SUMIFs only will do it if I have a range not individual cells to sum. Ideas? Thanks in advance. Mike |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF based on several criteria.
Hi Mike,
Assuming your data starts in A2, B2, C2, and runs down. In D2 (or any column on row 2) paste: =IF(AND(IF(A2=0,(B2+C2)/2,A2)100,OR(B2<90,C2<90)),100,IF(A2=0,(B2+C2)/2,A2))+IF(AND(IF(B2=0,(C2+A2)/2,B2)100,OR(C2<90,A2<90)),100,IF(B2=0,(C2+A2)/2,B2))+IF(AND(IF(C2=0,(A2+B2)/2,C2)100,OR(A2<90,B2<90)),100,IF(C2=0,(A2+B2)/2,C2)) Then drag/copy it down as far as you need. Cheers. Squeaky "Mike" wrote: OK here is what I want to do. I have three different values that I want to sum. I have several criteria that I have to consider when I sum them. A B C D Sum Total 89 90 105 100 100 110 75 0 85 75 65 0 Go 65 75 0 No/Go OK so here is a sample set of values. I want to sum the values from left to right. For the majority of my totals I will only need to do a straight sum(A,B,C) no big deal. However from time to time as in the first example where run into a qualification. Problem 1. If any of the three values are under 90 and any of the other values are over 100 I need to round the value over 100 down to 100. Problem 2. If any of the the values are a 0 then I need to be able in the same cell average the other 2 number and then add the three values together. I have tried to use nested If statments, looked at SUMIFS and SUMIF but the SUMIFs only will do it if I have a range not individual cells to sum. Ideas? Thanks in advance. Mike |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF based on several criteria.
Squeaky.
Thanks for the post. The only issue that I have is how do I incorperate the GO / NO GO into the overall statement. I was not clear when I wrote the post so I apologize up front. There were there problems that I had, you got two of the three. The third is to incorperate a GO or NO GO in column D. How it works is if column C is zero then I need it to look at column d and see if it is GO or NO GO. If it is a GO I need for it to average A and B and add it to A and B for an overall total. If it is a NO GO I need it to just add A and B. There are also times where I will only have A or B for a number and will have to validate with D as Go or No Go. If it is a Go then the total of either A or B time 3 will be the final number in the block. If this is confusing let me know and I will attempt to claify. Thanks. Mike "Squeaky" wrote: Hi Mike, Assuming your data starts in A2, B2, C2, and runs down. In D2 (or any column on row 2) paste: =IF(AND(IF(A2=0,(B2+C2)/2,A2)100,OR(B2<90,C2<90)),100,IF(A2=0,(B2+C2)/2,A2))+IF(AND(IF(B2=0,(C2+A2)/2,B2)100,OR(C2<90,A2<90)),100,IF(B2=0,(C2+A2)/2,B2))+IF(AND(IF(C2=0,(A2+B2)/2,C2)100,OR(A2<90,B2<90)),100,IF(C2=0,(A2+B2)/2,C2)) Then drag/copy it down as far as you need. Cheers. Squeaky "Mike" wrote: OK here is what I want to do. I have three different values that I want to sum. I have several criteria that I have to consider when I sum them. A B C D Sum Total 89 90 105 100 100 110 75 0 85 75 65 0 Go 65 75 0 No/Go OK so here is a sample set of values. I want to sum the values from left to right. For the majority of my totals I will only need to do a straight sum(A,B,C) no big deal. However from time to time as in the first example where run into a qualification. Problem 1. If any of the three values are under 90 and any of the other values are over 100 I need to round the value over 100 down to 100. Problem 2. If any of the the values are a 0 then I need to be able in the same cell average the other 2 number and then add the three values together. I have tried to use nested If statments, looked at SUMIFS and SUMIF but the SUMIFs only will do it if I have a range not individual cells to sum. Ideas? Thanks in advance. Mike |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF based on several criteria.
Hi Mike,
Yes, try to re-explain what you need. Here is what I understand: If any entry is zero you need for the form to average the other two numbers, replace the zero with that number, then add all three except if the zero is in the C column, where it will look to column D for either GO or NOGO. If it is GO then it will work as above, if NOGO then just add A and B, no averaging in C. "There are also times where I will only have A or B for a number and will have to validate with D as Go or No Go. If it is a Go then the total of either A or B time 3 will be the final number in the block." Not sure what the rest is you are trying to explain. Times 3? "Mike" wrote: Squeaky. Thanks for the post. The only issue that I have is how do I incorperate the GO / NO GO into the overall statement. I was not clear when I wrote the post so I apologize up front. There were there problems that I had, you got two of the three. The third is to incorperate a GO or NO GO in column D. How it works is if column C is zero then I need it to look at column d and see if it is GO or NO GO. If it is a GO I need for it to average A and B and add it to A and B for an overall total. If it is a NO GO I need it to just add A and B. There are also times where I will only have A or B for a number and will have to validate with D as Go or No Go. If it is a Go then the total of either A or B time 3 will be the final number in the block. If this is confusing let me know and I will attempt to claify. Thanks. Mike "Squeaky" wrote: Hi Mike, Assuming your data starts in A2, B2, C2, and runs down. In D2 (or any column on row 2) paste: =IF(AND(IF(A2=0,(B2+C2)/2,A2)100,OR(B2<90,C2<90)),100,IF(A2=0,(B2+C2)/2,A2))+IF(AND(IF(B2=0,(C2+A2)/2,B2)100,OR(C2<90,A2<90)),100,IF(B2=0,(C2+A2)/2,B2))+IF(AND(IF(C2=0,(A2+B2)/2,C2)100,OR(A2<90,B2<90)),100,IF(C2=0,(A2+B2)/2,C2)) Then drag/copy it down as far as you need. Cheers. Squeaky "Mike" wrote: OK here is what I want to do. I have three different values that I want to sum. I have several criteria that I have to consider when I sum them. A B C D Sum Total 89 90 105 100 100 110 75 0 85 75 65 0 Go 65 75 0 No/Go OK so here is a sample set of values. I want to sum the values from left to right. For the majority of my totals I will only need to do a straight sum(A,B,C) no big deal. However from time to time as in the first example where run into a qualification. Problem 1. If any of the three values are under 90 and any of the other values are over 100 I need to round the value over 100 down to 100. Problem 2. If any of the the values are a 0 then I need to be able in the same cell average the other 2 number and then add the three values together. I have tried to use nested If statments, looked at SUMIFS and SUMIF but the SUMIFs only will do it if I have a range not individual cells to sum. Ideas? Thanks in advance. Mike |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF based on several criteria.
Squealy,
In all cases add all three values together. If any value is zero and the other two values are not, average the two and add it to the sum of the first two. In all cases if C is zero, check D for Go/ No Go. if D is Go average and sum, else sum the first two values. If all values are zero, check D for G/NG and use the value in D. Let me know if it helps or if you need more. Thanks again. Mike Hi Mike, Yes, try to re-explain what you need. Here is what I understand: If any entry is zero you need for the form to average the other two numbers, replace the zero with that number, then add all three except if the zero is in the C column, where it will look to column D for either GO or NOGO. If it is GO then it will work as above, if NOGO then just add A and B, no averaging in C. "There are also times where I will only have A or B for a number and will have to validate with D as Go or No Go. If it is a Go then the total of either A or B time 3 will be the final number in the block." Not sure what the rest is you are trying to explain. Times 3? "Mike" wrote: Squeaky. Thanks for the post. The only issue that I have is how do I incorperate the GO / NO GO into the overall statement. I was not clear when I wrote the post so I apologize up front. There were there problems that I had, you got two of the three. The third is to incorperate a GO or NO GO in column D. How it works is if column C is zero then I need it to look at column d and see if it is GO or NO GO. If it is a GO I need for it to average A and B and add it to A and B for an overall total. If it is a NO GO I need it to just add A and B. There are also times where I will only have A or B for a number and will have to validate with D as Go or No Go. If it is a Go then the total of either A or B time 3 will be the final number in the block. If this is confusing let me know and I will attempt to claify. Thanks. Mike "Squeaky" wrote: Hi Mike, Assuming your data starts in A2, B2, C2, and runs down. In D2 (or any column on row 2) paste: =IF(AND(IF(A2=0,(B2+C2)/2,A2)100,OR(B2<90,C2<90)),100,IF(A2=0,(B2+C2)/2,A2))+IF(AND(IF(B2=0,(C2+A2)/2,B2)100,OR(C2<90,A2<90)),100,IF(B2=0,(C2+A2)/2,B2))+IF(AND(IF(C2=0,(A2+B2)/2,C2)100,OR(A2<90,B2<90)),100,IF(C2=0,(A2+B2)/2,C2)) Then drag/copy it down as far as you need. Cheers. Squeaky "Mike" wrote: OK here is what I want to do. I have three different values that I want to sum. I have several criteria that I have to consider when I sum them. A B C D Sum Total 89 90 105 100 100 110 75 0 85 75 65 0 Go 65 75 0 No/Go OK so here is a sample set of values. I want to sum the values from left to right. For the majority of my totals I will only need to do a straight sum(A,B,C) no big deal. However from time to time as in the first example where run into a qualification. Problem 1. If any of the three values are under 90 and any of the other values are over 100 I need to round the value over 100 down to 100. Problem 2. If any of the the values are a 0 then I need to be able in the same cell average the other 2 number and then add the three values together. I have tried to use nested If statments, looked at SUMIFS and SUMIF but the SUMIFs only will do it if I have a range not individual cells to sum. Ideas? Thanks in advance. Mike |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMIF based on several criteria.
Hi Mike,
Sorry, but I have been away. It looks like we are saying the same thing except where all values are zero. If D says GO or NOGO, how do I use the value in D? Squeaky. "Mike" wrote: Squealy, In all cases add all three values together. If any value is zero and the other two values are not, average the two and add it to the sum of the first two. In all cases if C is zero, check D for Go/ No Go. if D is Go average and sum, else sum the first two values. If all values are zero, check D for G/NG and use the value in D. Let me know if it helps or if you need more. Thanks again. Mike Hi Mike, Yes, try to re-explain what you need. Here is what I understand: If any entry is zero you need for the form to average the other two numbers, replace the zero with that number, then add all three except if the zero is in the C column, where it will look to column D for either GO or NOGO. If it is GO then it will work as above, if NOGO then just add A and B, no averaging in C. "There are also times where I will only have A or B for a number and will have to validate with D as Go or No Go. If it is a Go then the total of either A or B time 3 will be the final number in the block." Not sure what the rest is you are trying to explain. Times 3? "Mike" wrote: Squeaky. Thanks for the post. The only issue that I have is how do I incorperate the GO / NO GO into the overall statement. I was not clear when I wrote the post so I apologize up front. There were there problems that I had, you got two of the three. The third is to incorperate a GO or NO GO in column D. How it works is if column C is zero then I need it to look at column d and see if it is GO or NO GO. If it is a GO I need for it to average A and B and add it to A and B for an overall total. If it is a NO GO I need it to just add A and B. There are also times where I will only have A or B for a number and will have to validate with D as Go or No Go. If it is a Go then the total of either A or B time 3 will be the final number in the block. If this is confusing let me know and I will attempt to claify. Thanks. Mike "Squeaky" wrote: Hi Mike, Assuming your data starts in A2, B2, C2, and runs down. In D2 (or any column on row 2) paste: =IF(AND(IF(A2=0,(B2+C2)/2,A2)100,OR(B2<90,C2<90)),100,IF(A2=0,(B2+C2)/2,A2))+IF(AND(IF(B2=0,(C2+A2)/2,B2)100,OR(C2<90,A2<90)),100,IF(B2=0,(C2+A2)/2,B2))+IF(AND(IF(C2=0,(A2+B2)/2,C2)100,OR(A2<90,B2<90)),100,IF(C2=0,(A2+B2)/2,C2)) Then drag/copy it down as far as you need. Cheers. Squeaky "Mike" wrote: OK here is what I want to do. I have three different values that I want to sum. I have several criteria that I have to consider when I sum them. A B C D Sum Total 89 90 105 100 100 110 75 0 85 75 65 0 Go 65 75 0 No/Go OK so here is a sample set of values. I want to sum the values from left to right. For the majority of my totals I will only need to do a straight sum(A,B,C) no big deal. However from time to time as in the first example where run into a qualification. Problem 1. If any of the three values are under 90 and any of the other values are over 100 I need to round the value over 100 down to 100. Problem 2. If any of the the values are a 0 then I need to be able in the same cell average the other 2 number and then add the three values together. I have tried to use nested If statments, looked at SUMIFS and SUMIF but the SUMIFs only will do it if I have a range not individual cells to sum. Ideas? Thanks in advance. Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I use sumif with OR to sum based on two column criteria? | Excel Worksheet Functions | |||
| SumIf formula from one column based on criteria from two other col | Excel Discussion (Misc queries) | |||
| sumif-add amount to another cell based on two criteria | Excel Discussion (Misc queries) | |||
| SumIf based on two criteria | Excel Worksheet Functions | |||
| Is it possible to use the sumif function based on the criteria of. | Excel Worksheet Functions | |||
Linear Mode
