Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use VLOOKUP in a sumproduct calculation
Hi,
Is it possible to use VLOOKUP in a SUMPRODUCT calculation? I have a large series of data and I want to count the number of instances where a row has several criteria - and I can nearly cover it in a single SUMPRODUCT formula. An example of the data I am counting is: Priority Included Achieved A Yes Q2 2007 A Yes Q1 2008 A No Q1 2008 B Yes Q1 2008 A Yes Q2 2008 and I am looking to count the number of priority A's that have a "Yes" in the column Included and were achieved in Q2 2008 or before. So in this example the count would be 3. The problem I have is that one of my criteria is that a milestone achieved was in the quarter I am looking at or before. The data in this column is in the form "Q[NUMBER] [YEAR]" (see example above). The way I am planning to do is to have a lookup table elsewhere which converts each quarter into a ranked number e.g.: Quarter Rank Q1 2008 1 Q2 2008 2 Q3 2008 3 Any ideas how to get the VLOOKUP to work in the SUMPRODUCT function - please help me!!! Sorry if its not that clear but let me know what clarification you need Thanks in advance Ed |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use VLOOKUP in a sumproduct calculation
Okay, I think this is what you want. I'll rephrase. You want total occurances
that match the criteria of exact match of priority (=A), was included (=Yes), and was achieved during or before a particular Quarter (<=Q2 2008) Assuming your sample data is in A2:C6, I have the criteria as follows: A8:C8 = A | Yes | Q2 2008 I have the formula in A9 as follows: =SUMPRODUCT(--($A$2:$A$6=A8),--($B$2:$B$6=B8),--(RIGHT($C$2:$C$6,4)+MID($C$2:$C$6,2,1)/10<=RIGHT(C8,4)+MID(C8,2,1)/10)) Effectively, I convert the Quarters into a number of YEAR.Qtr, so Q2 2008 becomes 2008.2, then if that is less than or equal to the range C2:C6 which is also converted, then it would have occurred during or before that quarter, and would therefore count it. If this is not what you were trying for, then please try to give a little more detail. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "edeaston" wrote: Hi, Is it possible to use VLOOKUP in a SUMPRODUCT calculation? I have a large series of data and I want to count the number of instances where a row has several criteria - and I can nearly cover it in a single SUMPRODUCT formula. An example of the data I am counting is: Priority Included Achieved A Yes Q2 2007 A Yes Q1 2008 A No Q1 2008 B Yes Q1 2008 A Yes Q2 2008 and I am looking to count the number of priority A's that have a "Yes" in the column Included and were achieved in Q2 2008 or before. So in this example the count would be 3. The problem I have is that one of my criteria is that a milestone achieved was in the quarter I am looking at or before. The data in this column is in the form "Q[NUMBER] [YEAR]" (see example above). The way I am planning to do is to have a lookup table elsewhere which converts each quarter into a ranked number e.g.: Quarter Rank Q1 2008 1 Q2 2008 2 Q3 2008 3 Any ideas how to get the VLOOKUP to work in the SUMPRODUCT function - please help me!!! Sorry if its not that clear but let me know what clarification you need Thanks in advance Ed |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use VLOOKUP in a sumproduct calculation
Hi John,
Thanks for your quick response - your recap is spot on and your solution is very clever, something that I hadnt thought of. Sadly it didnt solve my problem - I forgot to mention some additional info which seems to prevent it from working! Not all rows of data will have a value in the achieved column - at the moment the cells have TBC which seems to cause an error in the formula. Even if I change all the TBCs to blank then the problem still exists. Any thoughts? Thanks Ed "John C" wrote: Okay, I think this is what you want. I'll rephrase. You want total occurances that match the criteria of exact match of priority (=A), was included (=Yes), and was achieved during or before a particular Quarter (<=Q2 2008) Assuming your sample data is in A2:C6, I have the criteria as follows: A8:C8 = A | Yes | Q2 2008 I have the formula in A9 as follows: =SUMPRODUCT(--($A$2:$A$6=A8),--($B$2:$B$6=B8),--(RIGHT($C$2:$C$6,4)+MID($C$2:$C$6,2,1)/10<=RIGHT(C8,4)+MID(C8,2,1)/10)) Effectively, I convert the Quarters into a number of YEAR.Qtr, so Q2 2008 becomes 2008.2, then if that is less than or equal to the range C2:C6 which is also converted, then it would have occurred during or before that quarter, and would therefore count it. If this is not what you were trying for, then please try to give a little more detail. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "edeaston" wrote: Hi, Is it possible to use VLOOKUP in a SUMPRODUCT calculation? I have a large series of data and I want to count the number of instances where a row has several criteria - and I can nearly cover it in a single SUMPRODUCT formula. An example of the data I am counting is: Priority Included Achieved A Yes Q2 2007 A Yes Q1 2008 A No Q1 2008 B Yes Q1 2008 A Yes Q2 2008 and I am looking to count the number of priority A's that have a "Yes" in the column Included and were achieved in Q2 2008 or before. So in this example the count would be 3. The problem I have is that one of my criteria is that a milestone achieved was in the quarter I am looking at or before. The data in this column is in the form "Q[NUMBER] [YEAR]" (see example above). The way I am planning to do is to have a lookup table elsewhere which converts each quarter into a ranked number e.g.: Quarter Rank Q1 2008 1 Q2 2008 2 Q3 2008 3 Any ideas how to get the VLOOKUP to work in the SUMPRODUCT function - please help me!!! Sorry if its not that clear but let me know what clarification you need Thanks in advance Ed |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use VLOOKUP in a sumproduct calculation
Which cells have TBC? Is that the Year/Qtr range or...?
-- ** John C ** "edeaston" wrote: Hi John, Thanks for your quick response - your recap is spot on and your solution is very clever, something that I hadnt thought of. Sadly it didnt solve my problem - I forgot to mention some additional info which seems to prevent it from working! Not all rows of data will have a value in the achieved column - at the moment the cells have TBC which seems to cause an error in the formula. Even if I change all the TBCs to blank then the problem still exists. Any thoughts? Thanks Ed "John C" wrote: Okay, I think this is what you want. I'll rephrase. You want total occurances that match the criteria of exact match of priority (=A), was included (=Yes), and was achieved during or before a particular Quarter (<=Q2 2008) Assuming your sample data is in A2:C6, I have the criteria as follows: A8:C8 = A | Yes | Q2 2008 I have the formula in A9 as follows: =SUMPRODUCT(--($A$2:$A$6=A8),--($B$2:$B$6=B8),--(RIGHT($C$2:$C$6,4)+MID($C$2:$C$6,2,1)/10<=RIGHT(C8,4)+MID(C8,2,1)/10)) Effectively, I convert the Quarters into a number of YEAR.Qtr, so Q2 2008 becomes 2008.2, then if that is less than or equal to the range C2:C6 which is also converted, then it would have occurred during or before that quarter, and would therefore count it. If this is not what you were trying for, then please try to give a little more detail. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "edeaston" wrote: Hi, Is it possible to use VLOOKUP in a SUMPRODUCT calculation? I have a large series of data and I want to count the number of instances where a row has several criteria - and I can nearly cover it in a single SUMPRODUCT formula. An example of the data I am counting is: Priority Included Achieved A Yes Q2 2007 A Yes Q1 2008 A No Q1 2008 B Yes Q1 2008 A Yes Q2 2008 and I am looking to count the number of priority A's that have a "Yes" in the column Included and were achieved in Q2 2008 or before. So in this example the count would be 3. The problem I have is that one of my criteria is that a milestone achieved was in the quarter I am looking at or before. The data in this column is in the form "Q[NUMBER] [YEAR]" (see example above). The way I am planning to do is to have a lookup table elsewhere which converts each quarter into a ranked number e.g.: Quarter Rank Q1 2008 1 Q2 2008 2 Q3 2008 3 Any ideas how to get the VLOOKUP to work in the SUMPRODUCT function - please help me!!! Sorry if its not that clear but let me know what clarification you need Thanks in advance Ed |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use VLOOKUP in a sumproduct calculation
Sorry - the Achieved column is the one that has TBC in which is the one with
the Qtr Year range. "John C" wrote: Which cells have TBC? Is that the Year/Qtr range or...? -- ** John C ** "edeaston" wrote: Hi John, Thanks for your quick response - your recap is spot on and your solution is very clever, something that I hadnt thought of. Sadly it didnt solve my problem - I forgot to mention some additional info which seems to prevent it from working! Not all rows of data will have a value in the achieved column - at the moment the cells have TBC which seems to cause an error in the formula. Even if I change all the TBCs to blank then the problem still exists. Any thoughts? Thanks Ed "John C" wrote: Okay, I think this is what you want. I'll rephrase. You want total occurances that match the criteria of exact match of priority (=A), was included (=Yes), and was achieved during or before a particular Quarter (<=Q2 2008) Assuming your sample data is in A2:C6, I have the criteria as follows: A8:C8 = A | Yes | Q2 2008 I have the formula in A9 as follows: =SUMPRODUCT(--($A$2:$A$6=A8),--($B$2:$B$6=B8),--(RIGHT($C$2:$C$6,4)+MID($C$2:$C$6,2,1)/10<=RIGHT(C8,4)+MID(C8,2,1)/10)) Effectively, I convert the Quarters into a number of YEAR.Qtr, so Q2 2008 becomes 2008.2, then if that is less than or equal to the range C2:C6 which is also converted, then it would have occurred during or before that quarter, and would therefore count it. If this is not what you were trying for, then please try to give a little more detail. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "edeaston" wrote: Hi, Is it possible to use VLOOKUP in a SUMPRODUCT calculation? I have a large series of data and I want to count the number of instances where a row has several criteria - and I can nearly cover it in a single SUMPRODUCT formula. An example of the data I am counting is: Priority Included Achieved A Yes Q2 2007 A Yes Q1 2008 A No Q1 2008 B Yes Q1 2008 A Yes Q2 2008 and I am looking to count the number of priority A's that have a "Yes" in the column Included and were achieved in Q2 2008 or before. So in this example the count would be 3. The problem I have is that one of my criteria is that a milestone achieved was in the quarter I am looking at or before. The data in this column is in the form "Q[NUMBER] [YEAR]" (see example above). The way I am planning to do is to have a lookup table elsewhere which converts each quarter into a ranked number e.g.: Quarter Rank Q1 2008 1 Q2 2008 2 Q3 2008 3 Any ideas how to get the VLOOKUP to work in the SUMPRODUCT function - please help me!!! Sorry if its not that clear but let me know what clarification you need Thanks in advance Ed |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use VLOOKUP in a sumproduct calculation
Ok, it's actually a very simple fix. The problem was I was doing addition and
division with that value, when, in reality, I could just put them together with an ampersand sign. 20081 would still be Qtr 1 2008, for example. So this should work for you: =SUMPRODUCT(--($A$2:$A$6=A8),--($B$2:$B$6=B8),--($C$2:$C$6<"TBC"),--(RIGHT($C$2:$C$6,4)&MID($C$2:$C$6,2,1)<=RIGHT(C8,4 )&MID(C8,2,1))) -- ** John C ** "edeaston" wrote: Sorry - the Achieved column is the one that has TBC in which is the one with the Qtr Year range. "John C" wrote: Which cells have TBC? Is that the Year/Qtr range or...? -- ** John C ** "edeaston" wrote: Hi John, Thanks for your quick response - your recap is spot on and your solution is very clever, something that I hadnt thought of. Sadly it didnt solve my problem - I forgot to mention some additional info which seems to prevent it from working! Not all rows of data will have a value in the achieved column - at the moment the cells have TBC which seems to cause an error in the formula. Even if I change all the TBCs to blank then the problem still exists. Any thoughts? Thanks Ed "John C" wrote: Okay, I think this is what you want. I'll rephrase. You want total occurances that match the criteria of exact match of priority (=A), was included (=Yes), and was achieved during or before a particular Quarter (<=Q2 2008) Assuming your sample data is in A2:C6, I have the criteria as follows: A8:C8 = A | Yes | Q2 2008 I have the formula in A9 as follows: =SUMPRODUCT(--($A$2:$A$6=A8),--($B$2:$B$6=B8),--(RIGHT($C$2:$C$6,4)+MID($C$2:$C$6,2,1)/10<=RIGHT(C8,4)+MID(C8,2,1)/10)) Effectively, I convert the Quarters into a number of YEAR.Qtr, so Q2 2008 becomes 2008.2, then if that is less than or equal to the range C2:C6 which is also converted, then it would have occurred during or before that quarter, and would therefore count it. If this is not what you were trying for, then please try to give a little more detail. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "edeaston" wrote: Hi, Is it possible to use VLOOKUP in a SUMPRODUCT calculation? I have a large series of data and I want to count the number of instances where a row has several criteria - and I can nearly cover it in a single SUMPRODUCT formula. An example of the data I am counting is: Priority Included Achieved A Yes Q2 2007 A Yes Q1 2008 A No Q1 2008 B Yes Q1 2008 A Yes Q2 2008 and I am looking to count the number of priority A's that have a "Yes" in the column Included and were achieved in Q2 2008 or before. So in this example the count would be 3. The problem I have is that one of my criteria is that a milestone achieved was in the quarter I am looking at or before. The data in this column is in the form "Q[NUMBER] [YEAR]" (see example above). The way I am planning to do is to have a lookup table elsewhere which converts each quarter into a ranked number e.g.: Quarter Rank Q1 2008 1 Q2 2008 2 Q3 2008 3 Any ideas how to get the VLOOKUP to work in the SUMPRODUCT function - please help me!!! Sorry if its not that clear but let me know what clarification you need Thanks in advance Ed |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use VLOOKUP in a sumproduct calculation
That little update has fixed it - thanks for all your help!
Ed "John C" wrote: Ok, it's actually a very simple fix. The problem was I was doing addition and division with that value, when, in reality, I could just put them together with an ampersand sign. 20081 would still be Qtr 1 2008, for example. So this should work for you: =SUMPRODUCT(--($A$2:$A$6=A8),--($B$2:$B$6=B8),--($C$2:$C$6<"TBC"),--(RIGHT($C$2:$C$6,4)&MID($C$2:$C$6,2,1)<=RIGHT(C8,4 )&MID(C8,2,1))) -- ** John C ** "edeaston" wrote: Sorry - the Achieved column is the one that has TBC in which is the one with the Qtr Year range. "John C" wrote: Which cells have TBC? Is that the Year/Qtr range or...? -- ** John C ** "edeaston" wrote: Hi John, Thanks for your quick response - your recap is spot on and your solution is very clever, something that I hadnt thought of. Sadly it didnt solve my problem - I forgot to mention some additional info which seems to prevent it from working! Not all rows of data will have a value in the achieved column - at the moment the cells have TBC which seems to cause an error in the formula. Even if I change all the TBCs to blank then the problem still exists. Any thoughts? Thanks Ed "John C" wrote: Okay, I think this is what you want. I'll rephrase. You want total occurances that match the criteria of exact match of priority (=A), was included (=Yes), and was achieved during or before a particular Quarter (<=Q2 2008) Assuming your sample data is in A2:C6, I have the criteria as follows: A8:C8 = A | Yes | Q2 2008 I have the formula in A9 as follows: =SUMPRODUCT(--($A$2:$A$6=A8),--($B$2:$B$6=B8),--(RIGHT($C$2:$C$6,4)+MID($C$2:$C$6,2,1)/10<=RIGHT(C8,4)+MID(C8,2,1)/10)) Effectively, I convert the Quarters into a number of YEAR.Qtr, so Q2 2008 becomes 2008.2, then if that is less than or equal to the range C2:C6 which is also converted, then it would have occurred during or before that quarter, and would therefore count it. If this is not what you were trying for, then please try to give a little more detail. -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "edeaston" wrote: Hi, Is it possible to use VLOOKUP in a SUMPRODUCT calculation? I have a large series of data and I want to count the number of instances where a row has several criteria - and I can nearly cover it in a single SUMPRODUCT formula. An example of the data I am counting is: Priority Included Achieved A Yes Q2 2007 A Yes Q1 2008 A No Q1 2008 B Yes Q1 2008 A Yes Q2 2008 and I am looking to count the number of priority A's that have a "Yes" in the column Included and were achieved in Q2 2008 or before. So in this example the count would be 3. The problem I have is that one of my criteria is that a milestone achieved was in the quarter I am looking at or before. The data in this column is in the form "Q[NUMBER] [YEAR]" (see example above). The way I am planning to do is to have a lookup table elsewhere which converts each quarter into a ranked number e.g.: Quarter Rank Q1 2008 1 Q2 2008 2 Q3 2008 3 Any ideas how to get the VLOOKUP to work in the SUMPRODUCT function - please help me!!! Sorry if its not that clear but let me know what clarification you need Thanks in advance Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct calculation for January 09 | Excel Worksheet Functions | |||
Calculation using VLookup | Excel Worksheet Functions | |||
Advance Sumproduct calculation | Excel Worksheet Functions | |||
Sumproduct calculation between two workbooks | Excel Worksheet Functions | |||
returns calculation using sumproduct | Excel Worksheet Functions |