Adding cells in a specific date range

[sarahrose720]

I have a spreadsheet where column A contains dates, column G contains dollar amounts and column H contains general numbers. I will need to add the dollar amounts for a specific date, as well as the numbers in column H.

Right now I go in manually using Autosum, and highlight all of the dollar amounts for the specific date I am working on, and I do the same for the general numbers.

Is there an easier way to do this?

 

[Weaver]

Welcome to the board!

What I always do is have some free rows at the top of the sheet for totals etc, saves scrolling down all the time. So, assuming you've done the same, and your headers are on row 4, say:

In A1 = the start date
in A2 = the end date

Then let's say your data will never go below row 5000

To add your dollar amounts:

=sumproduct(--(A5:A5000>=A1),--(A5:A5000<=A2),G5:G5000)

and similarly for the general numbers:

=sumproduct(--(A5:A5000>=A1),--(A5:A5000<=A2),H5:H5000)

You can change the ranges to suit and even move where you drop the start and end times. You can even build them into the formula, but it is easier to have them in separate cells. What you can't do, unless you have 2007 or better is to use entire columns for the ranges in the sumproduct formula.

HTH

 

[T. Valko]

I have a spreadsheet where column A contains dates, column G contains dollar amounts and column H contains general numbers. I will need to add the dollar amounts for a specific date, as well as the numbers in column H.

Right now I go in manually using Autosum, and highlight all of the dollar amounts for the specific date I am working on, and I do the same for the general numbers.

Is there an easier way to do this?

What version of Excel are you using?