Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
Hello!
I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
Try sumproduct.
=SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do
you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
With all due respect, "it didnt work" isn't a desperately useful description
of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
Hi David,
I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
With that formula, you would need to have defined names for value1 and
value2. What John presumably intended you to do was to replace value1 and value2 in the formula either by numbers which you were looking for, or by text strings, or by a reference to the cells in which the values you are looking for would be found. So perhaps =SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100)) -- David Biddulph "Edinburgh" wrote in message ... Hi David, I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
Hi David
I'd like to jump in on this answer if I may? I want something similar to Edinburgh except the value I want to return from column C is a text value. Is there a formula that I can use that will do this for me, similar to sumproduct but obviously not producing sums...??? -- Marie Bayes "David Biddulph" wrote: With that formula, you would need to have defined names for value1 and value2. What John presumably intended you to do was to replace value1 and value2 in the formula either by numbers which you were looking for, or by text strings, or by a reference to the cells in which the values you are looking for would be found. So perhaps =SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100)) -- David Biddulph "Edinburgh" wrote in message ... Hi David, I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
If you are just looking at an individual row, and not producing sums, then
it sounds as if you just want =IF(AND(A1=42,B1=99),C1,"") or =IF(AND(A1="value1",B1="value2"),C1,"") or =IF(AND(A1=D$1,B1=E$1),C1,"") In each case I have assumed that you want a blank result if the conditions aren't satisfied. If you want a different response, put it instead of the "" towards the end of the IF(...) formula. -- David Biddulph "Marie Bayes" wrote in message ... Hi David I'd like to jump in on this answer if I may? I want something similar to Edinburgh except the value I want to return from column C is a text value. Is there a formula that I can use that will do this for me, similar to sumproduct but obviously not producing sums...??? -- Marie Bayes "David Biddulph" wrote: With that formula, you would need to have defined names for value1 and value2. What John presumably intended you to do was to replace value1 and value2 in the formula either by numbers which you were looking for, or by text strings, or by a reference to the cells in which the values you are looking for would be found. So perhaps =SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100)) -- David Biddulph "Edinburgh" wrote in message ... Hi David, I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
Hi David
That's almost what I require, except I think I need it to do a lookup. Let me explain in a bit more detail! Column A has a reference number, column B has a question and column C has the answer to the question in Column B. Column A reference number refers to a set of data, so for each reference number in Column A there are 17 Questions in Column B, all with a unique answer in Column C. I'm trying to transpose the data so that I get one set of data in one row. I'm hoping you can help as this spreadsheet is huge!!! Here's an example of the data: COLUMN A COLUMN B COLUMN C 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Accommodation type required Twin 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Any special dietary requirements No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Any special dietary requirements No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 County Midlothian 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 County Midlothian 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Date of Birth DATE1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Date of Birth Date2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you have any disabilities or medical conditions that we should be aware of No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you have any disabilities or medical conditions that we should be aware of No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you require any special access No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you require any special access No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Email email address 1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Email email address 2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Emergency contact details Cont 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Emergency contact details Cont 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First Name Patricia 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First Name Rosaleen 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First name as in passport Patricia Anne 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First name as in passport Rosaleen 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home Address Address 1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home Address Address 2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home contact number Number 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home contact number Number 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Known as Patti 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Known as Rosie 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Mobile Number Another Number 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Mobile Number Another Number 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Nationality British 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Nationality British 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Expiry date Oct 2016 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Expiry date May 2014 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Number Pass No 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Number Pass No 124 -- Marie Bayes "David Biddulph" wrote: If you are just looking at an individual row, and not producing sums, then it sounds as if you just want =IF(AND(A1=42,B1=99),C1,"") or =IF(AND(A1="value1",B1="value2"),C1,"") or =IF(AND(A1=D$1,B1=E$1),C1,"") In each case I have assumed that you want a blank result if the conditions aren't satisfied. If you want a different response, put it instead of the "" towards the end of the IF(...) formula. -- David Biddulph "Marie Bayes" wrote in message ... Hi David I'd like to jump in on this answer if I may? I want something similar to Edinburgh except the value I want to return from column C is a text value. Is there a formula that I can use that will do this for me, similar to sumproduct but obviously not producing sums...??? -- Marie Bayes "David Biddulph" wrote: With that formula, you would need to have defined names for value1 and value2. What John presumably intended you to do was to replace value1 and value2 in the formula either by numbers which you were looking for, or by text strings, or by a reference to the cells in which the values you are looking for would be found. So perhaps =SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100)) -- David Biddulph "Edinburgh" wrote in message ... Hi David, I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
I thought it may help to change the formula to
=IF(AND(F:F=$A:$A,G$1=$B:$B),$C:$C,"") and use CTRL/Shift to make it an array, but still not returning the results I require... -- Marie Bayes "David Biddulph" wrote: If you are just looking at an individual row, and not producing sums, then it sounds as if you just want =IF(AND(A1=42,B1=99),C1,"") or =IF(AND(A1="value1",B1="value2"),C1,"") or =IF(AND(A1=D$1,B1=E$1),C1,"") In each case I have assumed that you want a blank result if the conditions aren't satisfied. If you want a different response, put it instead of the "" towards the end of the IF(...) formula. -- David Biddulph "Marie Bayes" wrote in message ... Hi David I'd like to jump in on this answer if I may? I want something similar to Edinburgh except the value I want to return from column C is a text value. Is there a formula that I can use that will do this for me, similar to sumproduct but obviously not producing sums...??? -- Marie Bayes "David Biddulph" wrote: With that formula, you would need to have defined names for value1 and value2. What John presumably intended you to do was to replace value1 and value2 in the formula either by numbers which you were looking for, or by text strings, or by a reference to the cells in which the values you are looking for would be found. So perhaps =SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100)) -- David Biddulph "Edinburgh" wrote in message ... Hi David, I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
If I understand correctly what you want, sort your 3 columns (ABC) together
by column A, so that you have your 17 sets of identical column A values in consecutive rows, with the 17 sets of column B questions and column C answers alongside them. If what you want is to see the column A reference (A1) in D1 then the first column B question (B1) in E1, followed by its answer (C1) in F1, then the second question (B2) in G1, and its answer (C2) in H1, and so on, use the following formulae. In D1 =OFFSET($A$1,(ROW(A1)-1)*17,0) In E1 =OFFSET($B$1,(COLUMN(E1)-COLUMN($E$1))/2+(ROW(A1)-1)*17,MOD(COLUMN(E1)-COLUMN($E1),2)) Copy E1 across as far as AL1, which should be the column for answer C17 Then copy D1:AL1 down for as many rows as there are sets of questions. -- David Biddulph "Marie Bayes" wrote in message ... Hi David That's almost what I require, except I think I need it to do a lookup. Let me explain in a bit more detail! Column A has a reference number, column B has a question and column C has the answer to the question in Column B. Column A reference number refers to a set of data, so for each reference number in Column A there are 17 Questions in Column B, all with a unique answer in Column C. I'm trying to transpose the data so that I get one set of data in one row. I'm hoping you can help as this spreadsheet is huge!!! Here's an example of the data: COLUMN A COLUMN B COLUMN C 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Accommodation type required Twin 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Any special dietary requirements No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Any special dietary requirements No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 County Midlothian 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 County Midlothian 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Date of Birth DATE1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Date of Birth Date2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you have any disabilities or medical conditions that we should be aware of No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you have any disabilities or medical conditions that we should be aware of No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you require any special access No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you require any special access No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Email email address 1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Email email address 2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Emergency contact details Cont 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Emergency contact details Cont 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First Name Patricia 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First Name Rosaleen 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First name as in passport Patricia Anne 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First name as in passport Rosaleen 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home Address Address 1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home Address Address 2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home contact number Number 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home contact number Number 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Known as Patti 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Known as Rosie 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Mobile Number Another Number 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Mobile Number Another Number 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Nationality British 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Nationality British 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Expiry date Oct 2016 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Expiry date May 2014 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Number Pass No 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Number Pass No 124 -- Marie Bayes "David Biddulph" wrote: If you are just looking at an individual row, and not producing sums, then it sounds as if you just want =IF(AND(A1=42,B1=99),C1,"") or =IF(AND(A1="value1",B1="value2"),C1,"") or =IF(AND(A1=D$1,B1=E$1),C1,"") In each case I have assumed that you want a blank result if the conditions aren't satisfied. If you want a different response, put it instead of the "" towards the end of the IF(...) formula. -- David Biddulph "Marie Bayes" wrote in message ... Hi David I'd like to jump in on this answer if I may? I want something similar to Edinburgh except the value I want to return from column C is a text value. Is there a formula that I can use that will do this for me, similar to sumproduct but obviously not producing sums...??? -- Marie Bayes "David Biddulph" wrote: With that formula, you would need to have defined names for value1 and value2. What John presumably intended you to do was to replace value1 and value2 in the formula either by numbers which you were looking for, or by text strings, or by a reference to the cells in which the values you are looking for would be found. So perhaps =SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100)) -- David Biddulph "Edinburgh" wrote in message ... Hi David, I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
Hi David
I love what you've done there, however, i'm sorry but it's not what I requi I have, in column G, used filter special to create a list of unique reference numbers from column A, I have then done the same with column B, but transposed these so that the unique 'questions' now appear in columns H to AI, what I need is to lookup the 'answers' from Column C, that relate to the reference number G1 and the question H1, then I1, then J1 etc Here's a simpler example of what I have in the spreadsheet A:C A B C Ref1 Q1 AnswerABC Ref2 Q1 AnswerDEF Ref1 Q2 AnswerGHI Ref3 Q1 AnswerJKL Ref1 Q3 AnswerMNO Ref2 Q2 AnswerPQR Ref3 Q3 AnswerSTU Then I want to see this like so: H I J K REFER Q1 Q2 Q3 Ref1 AnswerABC AnswerGHI AnswerMNO Ref2 AnswerDEF AnswerPQR Ref3 AnswerJKL AnswerSTU I've produced Column H, my unique reference nos, I've produced I1:AI1, my questions, now I need to match up the references and questions to the answers, phew, I think I might have just managed to explain it! I hope that makes sense and thanks for listening! -- Marie Bayes "David Biddulph" wrote: If I understand correctly what you want, sort your 3 columns (ABC) together by column A, so that you have your 17 sets of identical column A values in consecutive rows, with the 17 sets of column B questions and column C answers alongside them. If what you want is to see the column A reference (A1) in D1 then the first column B question (B1) in E1, followed by its answer (C1) in F1, then the second question (B2) in G1, and its answer (C2) in H1, and so on, use the following formulae. In D1 =OFFSET($A$1,(ROW(A1)-1)*17,0) In E1 =OFFSET($B$1,(COLUMN(E1)-COLUMN($E$1))/2+(ROW(A1)-1)*17,MOD(COLUMN(E1)-COLUMN($E1),2)) Copy E1 across as far as AL1, which should be the column for answer C17 Then copy D1:AL1 down for as many rows as there are sets of questions. -- David Biddulph "Marie Bayes" wrote in message ... Hi David That's almost what I require, except I think I need it to do a lookup. Let me explain in a bit more detail! Column A has a reference number, column B has a question and column C has the answer to the question in Column B. Column A reference number refers to a set of data, so for each reference number in Column A there are 17 Questions in Column B, all with a unique answer in Column C. I'm trying to transpose the data so that I get one set of data in one row. I'm hoping you can help as this spreadsheet is huge!!! Here's an example of the data: COLUMN A COLUMN B COLUMN C 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Accommodation type required Twin 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Any special dietary requirements No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Any special dietary requirements No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 County Midlothian 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 County Midlothian 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Date of Birth DATE1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Date of Birth Date2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you have any disabilities or medical conditions that we should be aware of No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you have any disabilities or medical conditions that we should be aware of No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you require any special access No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you require any special access No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Email email address 1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Email email address 2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Emergency contact details Cont 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Emergency contact details Cont 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First Name Patricia 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First Name Rosaleen 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First name as in passport Patricia Anne 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First name as in passport Rosaleen 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home Address Address 1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home Address Address 2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home contact number Number 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home contact number Number 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Known as Patti 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Known as Rosie 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Mobile Number Another Number 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Mobile Number Another Number 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Nationality British 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Nationality British 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Expiry date Oct 2016 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Expiry date May 2014 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Number Pass No 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Number Pass No 124 -- Marie Bayes "David Biddulph" wrote: If you are just looking at an individual row, and not producing sums, then it sounds as if you just want =IF(AND(A1=42,B1=99),C1,"") or =IF(AND(A1="value1",B1="value2"),C1,"") or =IF(AND(A1=D$1,B1=E$1),C1,"") In each case I have assumed that you want a blank result if the conditions aren't satisfied. If you want a different response, put it instead of the "" towards the end of the IF(...) formula. -- David Biddulph "Marie Bayes" wrote in message ... Hi David I'd like to jump in on this answer if I may? I want something similar to Edinburgh except the value I want to return from column C is a text value. Is there a formula that I can use that will do this for me, similar to sumproduct but obviously not producing sums...??? -- Marie Bayes "David Biddulph" wrote: With that formula, you would need to have defined names for value1 and value2. What John presumably intended you to do was to replace value1 and value2 in the formula either by numbers which you were looking for, or by text strings, or by a reference to the cells in which the values you are looking for would be found. So perhaps =SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100)) -- David Biddulph "Edinburgh" wrote in message ... Hi David, I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula - looks up two values?
You should be able to do that with a variation of the formula I suggested if
you sort the data appropriately. -- David Biddulph "Marie Bayes" wrote in message ... Hi David I love what you've done there, however, i'm sorry but it's not what I requi I have, in column G, used filter special to create a list of unique reference numbers from column A, I have then done the same with column B, but transposed these so that the unique 'questions' now appear in columns H to AI, what I need is to lookup the 'answers' from Column C, that relate to the reference number G1 and the question H1, then I1, then J1 etc Here's a simpler example of what I have in the spreadsheet A:C A B C Ref1 Q1 AnswerABC Ref2 Q1 AnswerDEF Ref1 Q2 AnswerGHI Ref3 Q1 AnswerJKL Ref1 Q3 AnswerMNO Ref2 Q2 AnswerPQR Ref3 Q3 AnswerSTU Then I want to see this like so: H I J K REFER Q1 Q2 Q3 Ref1 AnswerABC AnswerGHI AnswerMNO Ref2 AnswerDEF AnswerPQR Ref3 AnswerJKL AnswerSTU I've produced Column H, my unique reference nos, I've produced I1:AI1, my questions, now I need to match up the references and questions to the answers, phew, I think I might have just managed to explain it! I hope that makes sense and thanks for listening! -- Marie Bayes "David Biddulph" wrote: If I understand correctly what you want, sort your 3 columns (ABC) together by column A, so that you have your 17 sets of identical column A values in consecutive rows, with the 17 sets of column B questions and column C answers alongside them. If what you want is to see the column A reference (A1) in D1 then the first column B question (B1) in E1, followed by its answer (C1) in F1, then the second question (B2) in G1, and its answer (C2) in H1, and so on, use the following formulae. In D1 =OFFSET($A$1,(ROW(A1)-1)*17,0) In E1 =OFFSET($B$1,(COLUMN(E1)-COLUMN($E$1))/2+(ROW(A1)-1)*17,MOD(COLUMN(E1)-COLUMN($E1),2)) Copy E1 across as far as AL1, which should be the column for answer C17 Then copy D1:AL1 down for as many rows as there are sets of questions. -- David Biddulph "Marie Bayes" wrote in message ... Hi David That's almost what I require, except I think I need it to do a lookup. Let me explain in a bit more detail! Column A has a reference number, column B has a question and column C has the answer to the question in Column B. Column A reference number refers to a set of data, so for each reference number in Column A there are 17 Questions in Column B, all with a unique answer in Column C. I'm trying to transpose the data so that I get one set of data in one row. I'm hoping you can help as this spreadsheet is huge!!! Here's an example of the data: COLUMN A COLUMN B COLUMN C 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Accommodation type required Twin 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Any special dietary requirements No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Any special dietary requirements No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 County Midlothian 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 County Midlothian 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Date of Birth DATE1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Date of Birth Date2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you have any disabilities or medical conditions that we should be aware of No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you have any disabilities or medical conditions that we should be aware of No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Do you require any special access No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Do you require any special access No 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Email email address 1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Email email address 2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Emergency contact details Cont 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Emergency contact details Cont 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First Name Patricia 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First Name Rosaleen 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 First name as in passport Patricia Anne 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 First name as in passport Rosaleen 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home Address Address 1 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home Address Address 2 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Home contact number Number 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Home contact number Number 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Known as Patti 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Known as Rosie 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Mobile Number Another Number 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Mobile Number Another Number 124 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Nationality British 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Nationality British 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Expiry date Oct 2016 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Expiry date May 2014 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-1 Passport Number Pass No 123 41t71u4czyqhzfoxlwnq8zxt64kygkips2hiovrd--Patricia-2 Passport Number Pass No 124 -- Marie Bayes "David Biddulph" wrote: If you are just looking at an individual row, and not producing sums, then it sounds as if you just want =IF(AND(A1=42,B1=99),C1,"") or =IF(AND(A1="value1",B1="value2"),C1,"") or =IF(AND(A1=D$1,B1=E$1),C1,"") In each case I have assumed that you want a blank result if the conditions aren't satisfied. If you want a different response, put it instead of the "" towards the end of the IF(...) formula. -- David Biddulph "Marie Bayes" wrote in message ... Hi David I'd like to jump in on this answer if I may? I want something similar to Edinburgh except the value I want to return from column C is a text value. Is there a formula that I can use that will do this for me, similar to sumproduct but obviously not producing sums...??? -- Marie Bayes "David Biddulph" wrote: With that formula, you would need to have defined names for value1 and value2. What John presumably intended you to do was to replace value1 and value2 in the formula either by numbers which you were looking for, or by text strings, or by a reference to the cells in which the values you are looking for would be found. So perhaps =SUMPRODUCT(--($A$1:$A$100=42),--($B$1:$B$100=99),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100="value1"),--($B$1:$B$100="value2"),($C$1:$C$100)) or =SUMPRODUCT(--($A$1:$A$100=D$1),--($B$1:$B$100=E$1),($C$1:$C$100)) -- David Biddulph "Edinburgh" wrote in message ... Hi David, I realise it is unsatisfactory and I apologise. I'm not a technically minded person so this is all very mind-boggling to me! I have a sheet of data which details all the hours recorded by all employees who each have a 'grade' attached to them and their time is also recorded against the projects they are working on in that particular period. What I wanted was a formua that allowed me to return the total of hours worked by project but also by grade. I tried the exact formula John suggested =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned, but it only returned #N/A. I seem to have found a suitable solution that should allow me to get the results that I want, but thank you for your time anyway. Edinburgh "David Biddulph" wrote: With all due respect, "it didnt work" isn't a desperately useful description of your problem, and (short of clairvoyance) it doesn't give contributors to the group a great deal of hope of helping you to identify what you've done wrong. Exactly what formula did you use? What data values were in the cells leading into that formula? What result did you get? What result did you expect? -- David Biddulph "Edinburgh" wrote in message ... Thank you John C. Unfortunately it didnt work? I'm not sure why not? Do you have any other ideas?? "John C" wrote: Try sumproduct. =SUMPRODUCT(--($A$1:$A$100=value1),--($B$1:$B$100=value2),($C$1:$C$100)) where column C is the value you want returned. -- John C "Edinburgh" wrote: Hello! I need to write a formula that allows me to look up two values in a series of vertical lists and then returns a total value (sumif) from the same table array that statisfies both criteria. Something like a vlookup and an AND and a SUMIF althogether if there is such a beast? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formula to return all non-blank values | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
How can I fix values in cells calculated by formula in Excel | Excel Worksheet Functions | |||
Combobox - use values in excel formula | Excel Worksheet Functions | |||
Formula for Searching & matching two values in excel | Excel Discussion (Misc queries) |