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)