Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,358
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,358
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,358
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct calculation for January 09 Tina Excel Worksheet Functions 5 July 31st 08 12:21 PM
Calculation using VLookup casdaq Excel Worksheet Functions 2 September 20th 07 11:48 PM
Advance Sumproduct calculation Brad Excel Worksheet Functions 8 October 31st 06 09:32 PM
Sumproduct calculation between two workbooks Pieman Excel Worksheet Functions 0 March 9th 06 04:17 PM
returns calculation using sumproduct Stan Altshuller Excel Worksheet Functions 6 January 13th 05 12:25 AM


All times are GMT +1. The time now is 04:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"