Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
I have a spreadsheet where all entries in Col A are either "E" or "S". Col B
has 10 different possible entries. I need a formula to 1st, select every row that is an "E", then 2nd, out of that, select only the rows that match a criteria for Col B's possibilites and 3rd, using the rows that the 2nd step selected, add all of the values in Col C and generate a total. I have various modifications of IF and SUMIF and cannot get anything to work. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Say the data looks like:
E 1 88 S B 17 E C 89 S C 39 E A 50 S E 31 E 4 100 E 2 74 S 1 64 S B 98 E A 48 E 1 81 E D 81 S D 71 S C 44 S 3 26 E 1 77 S A 54 E 2 91 E 1 54 E D 57 S E 64 S D 29 E 2 31 S C 44 S 1 65 E A 19 E B 73 S D 78 E E 23 and we want the sum of column C for column A=E and column B=1. =SUMPRODUCT(--(A1:A100="E"),--(B1:B100=1),(C1:C100)) will display 300 -- Gary''s Student - gsnu200833 "Dave" wrote: I have a spreadsheet where all entries in Col A are either "E" or "S". Col B has 10 different possible entries. I need a formula to 1st, select every row that is an "E", then 2nd, out of that, select only the rows that match a criteria for Col B's possibilites and 3rd, using the rows that the 2nd step selected, add all of the values in Col C and generate a total. I have various modifications of IF and SUMIF and cannot get anything to work. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
On Feb 14, 10:21*pm, Dave wrote:
I have a spreadsheet where all entries in Col A are either "E" or "S". *Col B has 10 different possible entries. *I need a formula to 1st, select every row that is an "E", then 2nd, out of that, select only the rows that match a criteria for Col B's possibilites and 3rd, using the rows that the 2nd step selected, add all of the values in Col C and generate a total. I have various modifications of IF and SUMIF and cannot get anything to work. Any help would be greatly appreciated. First post on a new thread as you might be ignored sending your question on others. Second the best function to answer multiple conditioning is sumproduct Sumproduct(--(A1:A10="E"),--(B1:B10="put your criteria for column B here",C1:C10) For more info on sumproduct, go to http://www.xldynamic.com/source/xld.SUMPRODUCT.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Thank You! That worked great!
"Gary''s Student" wrote: Say the data looks like: E 1 88 S B 17 E C 89 S C 39 E A 50 S E 31 E 4 100 E 2 74 S 1 64 S B 98 E A 48 E 1 81 E D 81 S D 71 S C 44 S 3 26 E 1 77 S A 54 E 2 91 E 1 54 E D 57 S E 64 S D 29 E 2 31 S C 44 S 1 65 E A 19 E B 73 S D 78 E E 23 and we want the sum of column C for column A=E and column B=1. =SUMPRODUCT(--(A1:A100="E"),--(B1:B100=1),(C1:C100)) will display 300 -- Gary''s Student - gsnu200833 "Dave" wrote: I have a spreadsheet where all entries in Col A are either "E" or "S". Col B has 10 different possible entries. I need a formula to 1st, select every row that is an "E", then 2nd, out of that, select only the rows that match a criteria for Col B's possibilites and 3rd, using the rows that the 2nd step selected, add all of the values in Col C and generate a total. I have various modifications of IF and SUMIF and cannot get anything to work. Any help would be greatly appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Hi,
The best solution is always a matter of some issue - for example, if you arre using 2007 your best solution might be =SUMIFS(C1:C99,A1:A99,"E",B1:B99,15) where you want to sum column C if A is E and B is 15. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Rasoul Khoshravan" wrote: On Feb 14, 10:21 pm, Dave wrote: I have a spreadsheet where all entries in Col A are either "E" or "S". Col B has 10 different possible entries. I need a formula to 1st, select every row that is an "E", then 2nd, out of that, select only the rows that match a criteria for Col B's possibilites and 3rd, using the rows that the 2nd step selected, add all of the values in Col C and generate a total. I have various modifications of IF and SUMIF and cannot get anything to work. Any help would be greatly appreciated. First post on a new thread as you might be ignored sending your question on others. Second the best function to answer multiple conditioning is sumproduct Sumproduct(--(A1:A10="E"),--(B1:B10="put your criteria for column B here",C1:C10) For more info on sumproduct, go to http://www.xldynamic.com/source/xld.SUMPRODUCT.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|