Do I use Vlook Up here & if so how?

**karryan** · 04-27-2013, 05:08 PM

I know nothing about Vlookup and everywhere I read it's about #'s so I keep getting beyond confused. Then I think maybe a list is what I need. Can one of you amazing Excel Guru's take a look at what I am sure is a simple way to calculate how many waves are in completed/In Progress/Not started by WaveID?

I'd like to be able to do an array that will count how many waves by wave ID are within what status of running. See attached please:

**Lemice** · 04-27-2013, 05:15 PM

Hello,

VLOOKUP stands for Vertical Lookup, so Vlookup is mostly used to return a value basing on another value.

For your problem, COUNTIF is fine, for example, paste this Array formula on F2

=COUNTIF($C$2:$C$11,">"&TODAY())-COUNTIF($B$2:$B$11,">"&TODAY())

Because it's an Array formula, you need to hold Ctrl-Shift and hit Enter to enter it. If it's wrapped inside a { } then you did it right.
This formula will count how many Wave ID in progress.

This Array formula on F3 will count Wave ID that's completed

=COUNTIF($C$2:$C$11,"<="&TODAY())

And this normal formula will count how many Wave ID that hasn't started

=COUNTIF($B$2:$B$11,">"&TODAY())

Edit: I'm pretty sure the formula on Column F in your sample file does the same job, and you got it from a [SOLVED] thread right?
http://www.excelforum.com/excel-form...completed.html

**karryan** · 04-27-2013, 05:48 PM

Hi there - thanks for your quick reply. Yes the previous thread helped me immensely. I had split out only one area of the spreadsheet thinking it would help me but with this spreadsheet I inherited (and can't change too much the layout) it's a little more complex. see attached as I've removed confidential details and this should paint a much clearer picture. I'm trying to figure out how to report:

East has 2 waves in progress, 10 completed and 5 planned/not yet started
West has """
South West has ""

The data is in columns repeated in the workbook. So i spent almost an hour copying and pasting links into a sheet to tabulate all this and thought there must be a quicker way to collect this information from the source sheet? I'm worried about repasting information that it might compromise. The source document actually has up to Wave 20 so you can imagine how much scrolling is required -not ideal but I can't change the layout "yet".

Let me know if the format is something that can be worked at as is or if I do have to collect results into a separate sheet to run these #'s?

**Lemice** · 04-27-2013, 06:25 PM

This table is confusing me ...

So how do you tell East has 2 in progress, 10 completed and 5 planned / not yet started? From what I see East has a row, and in that row there's only 3 waves ID to check?

**karryan** · 04-29-2013, 02:36 PM

Hi there,

Right now it's strictly manual that I go an count by dates. I'm trying to figure out an IF formula that will return a status based upon the dates as follows:

1. If the Graduation Date has passed then its "completed"
2. If the Graduation Date has not passed then its "in progress"
3. If the start date has not yet passed then it's "Not Yet Started"

I'd have the formula to count across by the "Region". GTA East has 3 in progress, 2 graduated and 3 not yet begun.

Is this at all possible? I am horrible at IF statements for more then 2 scenarios.

Thanks so much Lem.

**Lemice** · 04-29-2013, 04:14 PM

Hello,

Please check if this sample file is what you are looking for.

In this sample, East has 1 "In Progress", 2 "Graduated" and 0 "Not Yet Started", and here is why

- For the 2 "Graduated", there are 3 cells in the same row as East that is not blank, under "Grad Date" column (Column I, M and Q) but 2 of them is smaller than TODAY() (M2 = December 20, 2012 and Q2 = January 23, 2013)

- For the 0 "Not Yet Started", there are 3 cells in the same row as East, under "Wave" column (Column G, K and O) but non of them is greater than TODAY().

- For the 1 "In Progress", I count the number of Wave column and minus the above.

Note that I use COLUMN() to point out which column is what, so if you increase number of column between each Wave column, you will have to increase the number inside the formula.

But first, let me know if this is what you are looking for.