Query

[Supriya]

Hey,

There are 3 columns of data.
Call Opened,Owner, Source
1)Now, i would like to pick up the total no of rows which has cal
opened date from 1st Feb to 7th Feb.
2) For each owner, total no of calls within 1st to 7th Fe

 

[Frank Kabel]

Hi
1.
=SUMPRODUCT((range_call_opened>=DATE(2004,2,1))*(range_call_opened<=DAT
E(2004,2,77)))
2.
=SUMPRODUCT((range_call_opened>=DATE(2004,2,1))*(range_call_opened<=DAT
E(2004,2,77))*(range_owner='Owner_name))

Frank

 

[Arvi Laanemets]

Hi

Define dynamic named ranges for CallOpened, Owner and Source, with
CallOpened as key column. Something like:
CallOpened=OFFSET(SheetName!$A$2,,,COUNTIF(SheetName!$A:$A,"<>")-1,1)
Owner=OFFSET(SheetName!$B$2,,,COUNTIF(SheetName!$A:$A,"<>")-1,1)
where SheetName is the name of sheet with your Data, and your table is in
columns A:C, with 1st row as column headers.

Formulas will be:
=SUMPRODUCT((CallOpened>=X1)*(CallOpened<=Y1))
with dates in X1:Y1
=SUMPRODUCT((CallOpened>=X1)*(CallOpened<=Y1)*(Owner=Z1))
with owner name in Z1