Multiple criterai using a common key, referencing multiple data sources

Hi there,

I have a data pool that uses the following hierachy

System (unique)
Tag (unique)
Sheet (not unique)

e.g. Every instance of a Sheet is associated with a Tag, and every Tag associated with a System

At the moment I can generate an excel spreadsheet that lists all of the tags against their systems, and a spreadsheet that lists all of the sheets against their tags. e.g.

Worksheet1
System; Tag
1; 1A
1; 1B
2; 2A
2; 2B

Worksheet2
Tag; Sheet; Completed Date
1A; Sheet1; nil
1A; Sheet2; nil
1B; Sheet1; 01/01/2014
1B: Sheet2; 01/01/2014
2A; Sheet2; 01/02/2014
2B; Sheet3; nil
2B; Sheet1; 01/03/2014
2B; Sheet2; 01/04/2014

I want to produce a formula that can count the total number of a particular Sheet in a particular System (using the Tag to link the two Worksheets) where the Completed Date is nil. I have been playing around with SUMPRODUCT but can't think of how to get this to work. As an example, what formula would I use to tell me that there is one Sheet2 in System 1 that has not been completed?

Any assistance would be greatly appreciated.

With your data like this:

System Tag Sheet Completed Date
1 1A Sheet1 nil
1 1A Sheet2 nil
1 1B Sheet1 1/01/2014
1 1B Sheet2 1/01/2014
2 2A Sheet2 1/02/2014
2 2B Sheet3 nil
2 2B Sheet1 1/03/2014
2 2B Sheet2 1/04/2014

=COUNTIFS(D:D,"nil",B:B,"=1*",C:C,"Sheet1") ===> gives you a count if nil / system 1 / sheet1

Obviously you can adjust the criteria as needed to return different combinations.

Thanks Danerida,

That's not quite what I am looking for. I am aware that I can use COUNTIF's if I was to put all of the data into a single worksheet. I'm more looking at a single formula that I can use with the data in its native format, which means I am not making extra work for myself each time I import the data.

What the formula needs to do is the following

for each line in Worksheet1
- if System = "1",
-- store var = value of Tag
-- for each line in Worksheet2
--- if Tag = var && Completed Date = "nil"
---- count++
--- close if
-- close for
- close if
close for

Originally Posted by mls82

As an example, what formula would I use to tell me that there is one Sheet2 in System 1 that has not been completed?

My formula does exactly that.

Originally Posted by mls82

Thanks Danerida,
That's not quite what I am looking for. I am aware that I can use COUNTIF's if I was to put all of the data into a single worksheet. I'm more looking at a single formula that I can use with the data in its native format, which means I am not making extra work for myself each time I import the data.

You are welcome, but I don't understand what the rest of this means. What is your data's native format?

You might be better served to put together a sample spreadsheet showing how you receive the data, and how you would like it to be laid out with your results.