SUMIFS with dates

[Chinni Krishna Reddy]

I have calls log in a sheet(All Call Details) with columns as follows:
Type,Date,Time,Number,"Duration/Volume",Amount,D,OperatorName,Day
respectively from A thru J.

In another sheet (Bill Summaries) i have two cells H5 and H6 with starting
and ending dates of this billing period. Now i want to retrieve all the call
charges with type "OG Local" and "OG National" within these dates. I used the
following function in one cell in Bill Summaries sheet.

=SUMIFS('All Call Details'!F:F,'All Call Details'!A:A,"OG*",'All Call
Details'!B:B,">'Bill Summaries'!H5",'All Call Details'!B:B,"<'Bill
Summaries'!H6")

The result is 0. I guess the problem is with dates. I tried giving them
directly but of no use.
Please note: I used SUMIFS from Excel 2007

 

[T. Valko]

Try this:

=SUMPRODUCT(--(LEFT('All Call Details'!A1:A100,2)="OG"),--('All Call
Details'!B1:B100>'Bill Summaries'!H5),--('All Call Details'!B1:B100<'Bill
Summaries'!H6),'All Call Details'!F1:F100)

Biff

 

[Harlan Grove]

Chinni Krishna Reddy wrote...
....

=SUMIFS('All Call Details'!F:F,'All Call Details'!A:A,"OG*",
'All Call Details'!B:B,">'Bill Summaries'!H5",
'All Call Details'!B:B,"<'Bill Summaries'!H6")

The result is 0. I guess the problem is with dates. I tried giving
them directly but of no use.
Please note: I used SUMIFS from Excel 2007

The problem is your date criteria syntax, not with the dates. The
criterion ">'Bill Summaries'!H5" is a comparison to the TEXT STRING
"'Bill Summaries'!H5" rather than the value in 'Bill Summaries'!H5.
Change your formula to

=SUMIFS('All Call Details'!F:F,'All Call Details'!A:A,"OG*",
'All Call Details'!B:B,">"&'Bill Summaries'!H5,
'All Call Details'!B:B,"<"&'Bill Summaries'!H6)