| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif??
I have 3 columns...
A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
=SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100))
-- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
What is the purpose of the dashes? My current result comes back as zero. Is
there a way for me to try part of the formula at a time to see which portion is not functioning correctly? "John C" wrote: =SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
In SUMPRODUCT, when you compare a cell range ($A$2:$A$100) to some value
(such as your description), this will force a series of TRUE or FALSE. The dashes change this value from TRUE or FALSE to 1 or 0. So you have 2 ranges that will be 1 or 0 depending on whether or not they meet the criteria, then the final range is the range you want 'summed'. Understand also that my 2 variables of description and equipment are in lowercase as you have to define those, either put the ones you are trying to count for in separate cells or 'hardcode' the formula itself Perhaps if you were to show your actual formula, and a snippet of your data (sample is fine), that 'should' be calculating some results. -- ** John C ** "Hope" wrote: What is the purpose of the dashes? My current result comes back as zero. Is there a way for me to try part of the formula at a time to see which portion is not functioning correctly? "John C" wrote: =SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
Somehow I fixed it. I don't really know how, but it's working now. Thanks
so much... "John C" wrote: In SUMPRODUCT, when you compare a cell range ($A$2:$A$100) to some value (such as your description), this will force a series of TRUE or FALSE. The dashes change this value from TRUE or FALSE to 1 or 0. So you have 2 ranges that will be 1 or 0 depending on whether or not they meet the criteria, then the final range is the range you want 'summed'. Understand also that my 2 variables of description and equipment are in lowercase as you have to define those, either put the ones you are trying to count for in separate cells or 'hardcode' the formula itself Perhaps if you were to show your actual formula, and a snippet of your data (sample is fine), that 'should' be calculating some results. -- ** John C ** "Hope" wrote: What is the purpose of the dashes? My current result comes back as zero. Is there a way for me to try part of the formula at a time to see which portion is not functioning correctly? "John C" wrote: =SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
If you post your formula(s), I can verify for you if everything will work
based on your conditions given, otherwise, glad you got it working :) -- ** John C ** "Hope" wrote: Somehow I fixed it. I don't really know how, but it's working now. Thanks so much... "John C" wrote: In SUMPRODUCT, when you compare a cell range ($A$2:$A$100) to some value (such as your description), this will force a series of TRUE or FALSE. The dashes change this value from TRUE or FALSE to 1 or 0. So you have 2 ranges that will be 1 or 0 depending on whether or not they meet the criteria, then the final range is the range you want 'summed'. Understand also that my 2 variables of description and equipment are in lowercase as you have to define those, either put the ones you are trying to count for in separate cells or 'hardcode' the formula itself Perhaps if you were to show your actual formula, and a snippet of your data (sample is fine), that 'should' be calculating some results. -- ** John C ** "Hope" wrote: What is the purpose of the dashes? My current result comes back as zero. Is there a way for me to try part of the formula at a time to see which portion is not functioning correctly? "John C" wrote: =SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
Hi John-
I'm having a similar problem that I hope you can help with- wanting to sum amounts in column B if column C has an R next to it: Example A B C 3 $40 17 $5 R 11 $10 R 32 $30 12 $10 R and so on..... Only want the $ amount for column B if C has an "R" Thank you in advance for your help! "John C" wrote: If you post your formula(s), I can verify for you if everything will work based on your conditions given, otherwise, glad you got it working :) -- ** John C ** "Hope" wrote: Somehow I fixed it. I don't really know how, but it's working now. Thanks so much... "John C" wrote: In SUMPRODUCT, when you compare a cell range ($A$2:$A$100) to some value (such as your description), this will force a series of TRUE or FALSE. The dashes change this value from TRUE or FALSE to 1 or 0. So you have 2 ranges that will be 1 or 0 depending on whether or not they meet the criteria, then the final range is the range you want 'summed'. Understand also that my 2 variables of description and equipment are in lowercase as you have to define those, either put the ones you are trying to count for in separate cells or 'hardcode' the formula itself Perhaps if you were to show your actual formula, and a snippet of your data (sample is fine), that 'should' be calculating some results. -- ** John C ** "Hope" wrote: What is the purpose of the dashes? My current result comes back as zero. Is there a way for me to try part of the formula at a time to see which portion is not functioning correctly? "John C" wrote: =SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
Hi
Try this : =SUMIF(C1:C5,"R",B1:B5) ajust range to your needs. HTH John "smilemdj72" wrote in message ... Hi John- I'm having a similar problem that I hope you can help with- wanting to sum amounts in column B if column C has an R next to it: Example A B C 3 $40 17 $5 R 11 $10 R 32 $30 12 $10 R and so on..... Only want the $ amount for column B if C has an "R" Thank you in advance for your help! "John C" wrote: If you post your formula(s), I can verify for you if everything will work based on your conditions given, otherwise, glad you got it working :) -- ** John C ** "Hope" wrote: Somehow I fixed it. I don't really know how, but it's working now. Thanks so much... "John C" wrote: In SUMPRODUCT, when you compare a cell range ($A$2:$A$100) to some value (such as your description), this will force a series of TRUE or FALSE. The dashes change this value from TRUE or FALSE to 1 or 0. So you have 2 ranges that will be 1 or 0 depending on whether or not they meet the criteria, then the final range is the range you want 'summed'. Understand also that my 2 variables of description and equipment are in lowercase as you have to define those, either put the ones you are trying to count for in separate cells or 'hardcode' the formula itself Perhaps if you were to show your actual formula, and a snippet of your data (sample is fine), that 'should' be calculating some results. -- ** John C ** "Hope" wrote: What is the purpose of the dashes? My current result comes back as zero. Is there a way for me to try part of the formula at a time to see which portion is not functioning correctly? "John C" wrote: =SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
Thank you for the fast response!! Exactly what I needed.
"John" wrote: Hi Try this : =SUMIF(C1:C5,"R",B1:B5) ajust range to your needs. HTH John "smilemdj72" wrote in message ... Hi John- I'm having a similar problem that I hope you can help with- wanting to sum amounts in column B if column C has an R next to it: Example A B C 3 $40 17 $5 R 11 $10 R 32 $30 12 $10 R and so on..... Only want the $ amount for column B if C has an "R" Thank you in advance for your help! "John C" wrote: If you post your formula(s), I can verify for you if everything will work based on your conditions given, otherwise, glad you got it working :) -- ** John C ** "Hope" wrote: Somehow I fixed it. I don't really know how, but it's working now. Thanks so much... "John C" wrote: In SUMPRODUCT, when you compare a cell range ($A$2:$A$100) to some value (such as your description), this will force a series of TRUE or FALSE. The dashes change this value from TRUE or FALSE to 1 or 0. So you have 2 ranges that will be 1 or 0 depending on whether or not they meet the criteria, then the final range is the range you want 'summed'. Understand also that my 2 variables of description and equipment are in lowercase as you have to define those, either put the ones you are trying to count for in separate cells or 'hardcode' the formula itself Perhaps if you were to show your actual formula, and a snippet of your data (sample is fine), that 'should' be calculating some results. -- ** John C ** "Hope" wrote: What is the purpose of the dashes? My current result comes back as zero. Is there a way for me to try part of the formula at a time to see which portion is not functioning correctly? "John C" wrote: =SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif??
Your Welcome
All the best for the NewYear John "smilemdj72" wrote in message ... Thank you for the fast response!! Exactly what I needed. "John" wrote: Hi Try this : =SUMIF(C1:C5,"R",B1:B5) ajust range to your needs. HTH John "smilemdj72" wrote in message ... Hi John- I'm having a similar problem that I hope you can help with- wanting to sum amounts in column B if column C has an R next to it: Example A B C 3 $40 17 $5 R 11 $10 R 32 $30 12 $10 R and so on..... Only want the $ amount for column B if C has an "R" Thank you in advance for your help! "John C" wrote: If you post your formula(s), I can verify for you if everything will work based on your conditions given, otherwise, glad you got it working :) -- ** John C ** "Hope" wrote: Somehow I fixed it. I don't really know how, but it's working now. Thanks so much... "John C" wrote: In SUMPRODUCT, when you compare a cell range ($A$2:$A$100) to some value (such as your description), this will force a series of TRUE or FALSE. The dashes change this value from TRUE or FALSE to 1 or 0. So you have 2 ranges that will be 1 or 0 depending on whether or not they meet the criteria, then the final range is the range you want 'summed'. Understand also that my 2 variables of description and equipment are in lowercase as you have to define those, either put the ones you are trying to count for in separate cells or 'hardcode' the formula itself Perhaps if you were to show your actual formula, and a snippet of your data (sample is fine), that 'should' be calculating some results. -- ** John C ** "Hope" wrote: What is the purpose of the dashes? My current result comes back as zero. Is there a way for me to try part of the formula at a time to see which portion is not functioning correctly? "John C" wrote: =SUMPRODUCT(--($A$2:$A$100=description),--($C$2:$C$100=equipment),($B$2:$B$100)) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Hope" wrote: I have 3 columns... A - defect description B - number of defects C - equipment I am trying to sum the number of defects if both the defect description and equipment match the criteria. Thanks in advance! Hope |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| SUMIF | Excel Worksheet Functions | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
