Use VLOOKUP in a sumproduct calculation

[edeaston]

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

 

[John C]

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.

 

[edeaston]

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

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.

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

 

[John C]

Which cells have TBC? Is that the Year/Qtr range or...?
--
** John C **

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

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.

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

 

[edeaston]

Sorry - the Achieved column is the one that has TBC in which is the one with
the Qtr Year range.

Which cells have TBC? Is that the Year/Qtr range or...?
--
** John C **

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

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.


:

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

 

[John C]

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 **

Sorry - the Achieved column is the one that has TBC in which is the one with
the Qtr Year range.

Which cells have TBC? Is that the Year/Qtr range or...?
--
** John C **

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

:

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.


:

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

 

[edeaston]

That little update has fixed it - thanks for all your help!

Ed

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 **

Sorry - the Achieved column is the one that has TBC in which is the one with
the Qtr Year range.

Which cells have TBC? Is that the Year/Qtr range or...?
--
** John C **


:

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

:

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.


:

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