Can I use > to compare dates within a COUNTIFS (or SUMPRODUCT maybe?)

[somanyqs]

A twist on an earlier post (wanted to post separately so not to mix everything into one...)

I have a list with companies, purchase dates, and what widget they purchased.
And also a flag for if the row was a Type1 deal or a Type2 deal (simple Yes/No) in different columns.

I then added a new column that says (Yes/No) whether that company has ever had BOTH Type1 or Type2 deals at any time ('Type1+2ever': in column F). Thanks Matty and ArthriticPanda for the help on that one:
F2=IF((COUNTIFS(A$2:A$12,A2,D$2:D$12,"Yes")>0)+(COUNTIFS(A$2:A$12,A2,E$2:E$12,"Yes")>0)=2,"Yes","No")

What I next can't figure out is how to add a new column [G] that checks to see if the company has ever had a Type2 deal anytime after a Type1 deal based on looking at the date in column B. If it has, then assign a "Yes" in column G for all of the rows for that company. So below, IBM and Nike get "Yes" in column G for all of their rows, but FedEx, Oracle and GE do not (GE is close, but the Type1 and Type2 occurred in the same quarter instead of Type 2 sometime after Type 1).

Co Date Widget Type1 Type2 Type1+2ever Type2after1
IBM 1/2013 Widget2 No Yes Yes Yes
IBM 2/2013 Widget3 No Yes Yes Yes
IBM 4/2012 Widget1 Yes No Yes Yes
FedEx 1/2013 Widget1 Yes No No No
FedEx 2/2013 Widget3 No No No No
FedEx 2/2013 Widget4 Yes No No No
Oracle 2/2013 Widget1 No Yes No No
Oracle 3/2013 Widget3 No No No No
Nike 2/2013 Widget1 No Yes Yes Yes
Nike 1/2013 Widget1 Yes Yes Yes Yes
Nike 2/2013 Widget1 No No Yes Yes
GE 2/2013 Widget2 No No Yes No
GE 3/2013 Widget3 Yes Yes Yes No
GE 2/2013 Widget1 No No Yes No

Apologies for pasting like this...I'm on a Mac (Excel 2011)
Thanks all!

 

[Jonmo1]

The syntax for using > and < operators in COUNTIFS is

">"&DATE(y,m,d)

Try
COUNTIFS(A$2:A$12,A2,D$2:D$12,"Yes",B$2:B$12,">="&DATE(2013,1,1))

That would count if the date in Column B was greater than or equal to Jan 1 2013.


Or you can put your date of interest in a cell, and use a cell reference in the formula.
Say Z1 = 1/1/2013
Then use
COUNTIFS(A$2:A$12,A2,D$2:D$12,"Yes",B$2:B$12,">="&$Z$1)

 

[somanyqs]

Since the final DATE(2013,1,1) isn't actually fixed, but it is dependent on the other dates for sales to the same company, if there is a way to capture that component and compare them?

So the formula would compare (hopefully I have this logic right):
for all of the rows for a certain company,
the "oldest" date for a company with a "Yes" (if any) in column E versus the date of the "Yes" (if any) in column D, and if the oldest date in E is more recent than the date in D then return a "Yes".

(phew, that is a lot for a Monday)