Count nonblank or counta for one column with a conditional in antoher column

[Churchy LaFemme]

I have a table of names (a few thousand rows of names) where there are also columns for the dates of trainings. What I ultimately want to do is determine which of the people have dates in both training columns.

There are four cases possible cases:
A) SpongeBob has a date for both the frist and the second training
B) Patrick has a date for only the first training
C) Patty has a date for only the second training
D) Squidward has no date entered for either traning.

So, if on the master sheet SpongeBob has a date in the column for training A, on my summary sheet, I would like to show if he has dates for both trainings.

I was able to produce the data I need in a pivot table and I can pull over the dates in two columns (on for each training) using and index-match nest, but I was wondering if there is a formula that would allow me to use one column to show a result on for those people who have had both trainings.

The following two theads did not get me there -
<TABLE style="WIDTH: 209pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=278><COLGROUP><COL style="WIDTH: 209pt; mso-width-source: userset; mso-width-alt: 10166" width=278><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 209pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=278>http://www.mrexcel.com/forum/showthread.php?t=552147&highlight=countif+conditional</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>http://www.mrexcel.com/forum/showthread.php?t=92672</TD></TR></TBODY></TABLE>

I thought about doing a Vlookup + Vlookup but didn't get very far. It seems that there should be a way to use Sumproduct or counta nested with a function that counts based on a name-match between the master sheet and my summary sheet, but I can't make this gel.

Any thoughs? This is not urgent - it just seems like an easy thing that people would do fairly often and yet it turns out I am clueless!

 

[Aladin Akyurek]

Can't think of any examples. I just recall cases where a sumproduct formula would work in theory with a limited same worksheet but then failed on my actual data.

Part of the issue might be excel having trouble with text - I suspect that when you have data compliled from different sources, even when it's all been trimmed and set to "general," some of the characters that appears the same to you and me and our eyeballs are not the same to excel.

In such cases, all functions would have trouble, not just SumProduct. By the way, SumProduct returns a number, if successful, not text.