SUMIFS with dates

  • Thread starter Chinni Krishna Reddy
  • Start date
C

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

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
 
H

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top