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