CountIf

[Noz2k]

Ok so, I have a column which counts the amount of times an id number appears in a table on sheet 2.

 =COUNTIF(Sheet2!$A$2:$A$1458,LostCustom!$A2)

but I want to add a further clause to this, which says to only count if the date in column d on sheet 2 is before the 15/03/2011.

Am struggling to get this to work, so any help would be appreciated.

 

[Aladin Akyurek]

Ok so, I have a column which counts the amount of times an id number appears in a table on sheet 2.

 =COUNTIF(Sheet2!$A$2:$A$1458,LostCustom!$A2)

but I want to add a further clause to this, which says to only count if the date in column d on sheet 2 is before the 15/03/2011.

Am struggling to get this to work, so any help would be appreciated.

You need a different function which can process multiple conditions...

On Excel 2007 or later:

=COUNTIFS(Sheet2!$A$2:$A$1458,LostCustom!$A2,Sheet2!$D$2:$D$1458,DATE(2011,3,15))

On all versions:

=SUMPRODUCT(--(Sheet2!$A$2:$A$1458=LostCustom!$A2),--(Sheet2!$D$2:$D$1458=DATE(2011,3,15)))

 

[Richard Schollar]

Hi

Depending on your version of Excel:

2007 or above:

=COUNTIFS(Sheet2!$D$2:$D$1458,"<" & DATE(2011,3,15),Sheet2!$A$2:$A$1458,LostCustom!$A2)

2003 or below:

=SUMPRODUCT((Sheet2!$D$2:$D$1458<DATE(2011,3,15))+0,(Sheet2!$A$2:$A$1458=LostCustom!$A2)+0)

 

[Noz2k]

Thanks, I was trying to use CountIfs, but wasn't using 'Date' was just trying <15/03/2011.

Your way works perfect though, thanks both of you for your help