How there a way to compare a range of date values on 1 sheet to a range of date values

I have 1 spread sheet that records the date of a transaction, the type and $ amount of the transaction and another where I balance the transactions against a bank statment and our cash application system
I wrote an argument that compares a control date and transaction type in the balancing sheet with the date and type in the detail sheet. if they are a match the argument brings back the $ amount of the transaction.

=IF((AND(Date='AMEX-MC-VISA'!$D21,Amex='AMEX-MC-VISA'!$B21)),'AMEX-MC-VISA'!$F21,0)

How do i modify this to bring back the $ amounts and transaction types for a range of sequential control dates i:e: 5/1 thru 5/7 rather than just a single date as the formula above does?

jayatwork

Hi,

Here's an example where you could use the SUMIFS function.

SumifsDateRange.xlsx

HTH
Steve

Originally Posted by SteveG

Hi,

Here's an example where you could use the SUMIFS function.

Attachment 158818

HTH
Steve

Thanks Steve-I'll look into that

Steve
Thanks for the example, but I'm using excel 2003 and the sample you provided just brings back "Name" and not a $ fig.
May, a really stupid question, but may I also conclude that if I use the same start and end date I will just get the data for that 1 date - I'm pretty sure that's true, but just wanted to check

jayatwork

Hi jayatwork,

I should have seen that you were using 2003. The SUMIFS formula was introduced with 2007. Here's a different formula you can use.

=SUMPRODUCT(--($A$2:$A$25>=E2),--($A$2:$A$25<=F2),--($B$2:$B$25=G2),--($C$2:$C$25))

And yes, if the dates are the same you'll only get that date's info.

HTH
Steve

Steve
Worked like a charm
Thanks Much!

Jay