Question

[PKinG]

hi,

I've developed a function that will count the number of items in a column between two dates. The function works fine at the moment:

=COUNTIF(Sheet1!U:U,"<="&DATE(2004,11,31))-COUNTIF(Sheet1!U:U,"<"&DATE(2004,11,1))

I now want to modify the function so it only counts between two dates if the name in column D matches for example 'Danny'. How would I go about this?

thanks

 

[Aladin Akyurek]

hi,

I've developed a function that will count the number of items in a column between two dates. The function works fine at the moment:

=COUNTIF(Sheet1!U:U,"<="&DATE(2004,11,31))-COUNTIF(Sheet1!U:U,"<"&DATE(2004,11,1))

I now want to modify the function so it only counts between two dates if the name in column D matches for example 'Danny'. How would I go about this?

thanks

With X2 housing a first day date like 1-Nov-2004 and Y2 a name like Danny...

=SUMPRODUCT(--(Sheet1!$U$2:$U$100-DAY(Sheet1!$U$2:$U$100)+1=X2),--(Sheet1!$D$2:$D$100=Y2))

Note that that this type of formulas do not admit whole columns like U:U as reference.