Prev: Need help with CountIF function
Next: #value! in linked document
From: Glyn on 14 May 2010 11:26
Hello,

A slightly different query for you..

I'm trying to use a countif on a particular "calendar", which shows days
across the sheet and named individuals down the sheet. The main content
shows what piece of work day by day each person is planned to be working on.
All "task" values entered are selected from drop-down lists the data for
which are sourced from a separate range in a second sheet (call it Jobs for
argument's sake).. so for example:

10/05 11/05 12/05 13/05
name1 defects defects defects P2 Build
name2 P1 Build CCR123 CCR123 P1 Build
name3 CCR49 CCR49 P2 Build P2 Build

We currently use some summary COUNTIF functions at the bottom of the first
worksheet to sum up:
- the number of people in a given day working on Project 1 build;
- the number of people working on Project 2 build;
- the number of people working on defects etc.

But some of the work (e.g. change control requests (CCRs)) may relate to P1
build or P2 build. So what I want to do (rather than amending the formula
throughout the year as new CCRs come in) is for each daily column, count up
all entries that show "P1 Build" AND in the same formula count any CCRs which
are categorised as "P1 Build" in the lookup table on the "Jobs" tab.

The range on the second tab looks like this:

Deliverable Workstream
CCR123 P1 Build
CCR456 P2 Build
CCR789 P1 Build
CCR889 etc. P2 Build
defects defects

etc.

The formula I'm currently trying (which is failing to do the lookup) is as
follows:

=COUNTIF(CW7:CW76,"P2 Build") +
COUNTIF(CW7:CW76,VLOOKUP(CW7:CW76,Jobs!A59:A122,2,FALSE)&"=P2 Build")

So count all instances of "P2 Build" in the main sheet, range CW7:CW76.
Add to this any entries from the main sheet for which the second column on
the Jobs sheet is set to "P2 Build".

Any suggestions appreciated...

Thank you

Glyn
 | 
Pages: 1
Prev: Need help with CountIF function
Next: #value! in linked document