Adding values in TWO columns between dates

[highflight1985]

I have the following formula:

=SUM(('Load Entry'!$C$4:$C$563<=$A$28)*('Load Entry'!$C$4:$C$563>=$A$27)*'Load Entry'!$S$4:$S$563)

to count the values in ONE column between two dates (A27 and A28)

But how do I count the values of two columns between the same dates? (Values to add together are S4 and U4, S5 and U5, etc.)

Thanks

 

[T. Valko]

I have the following formula:

=SUM(('Load Entry'!$C$4:$C$563<=$A$28)*('Load Entry'!$C$4:$C$563>=$A$27)*'Load Entry'!$S$4:$S$563)

to count the values in ONE column between two dates (A27 and A28)

But how do I count the values of two columns between the same dates? (Values to add together are S4 and U4, S5 and U5, etc.)

Thanks

You mean you want the SUM?

Try it like this...

=SUMPRODUCT(--('Load Entry'!$C$4:$C$563>=$A$27),--('Load Entry'!$C$4:$C$563 < =$A$28),'Load Entry'!$S$4:$S$563+'Load Entry'!$U$4:$U$563)

 

[highflight1985]

Yes, I do want the sum of the two columns...

I'd say you were an MVP again, but that didn't quite work...

It was giving me a value that wasn't quite correct. So, I compared it side by side with the formula I was using for the sum of the single column and noticed you had swapped the dates and the greater than/less than signs a bit. Not really sure why it mattered (as I'm dumb about all this, as you can tell by now), but I tried putting them back to the way they were in the single column summation. It worked...it's now giving me the correct sum.

Here's the new formula in case someone else needs it for their project...

=SUMPRODUCT(--('Load Entry'!$C$4:$C$563<=$A$28),--('Load Entry'!$C$4:$C$563>=$A$27),'Load Entry'!$S$4:$S$563+'Load Entry'!$U$4:$U$563)

For reference: C4-C563 are the columns that contain the dates to search through. A27 and A28 are the start and end date parameters. S and U are the columns to sum together if they fall within the dates specified.

Thanks Valko. Okay, you really are an MVP. I would have never figured all this out today that you have helped me with. I really appreciate it.

 

[T. Valko]

Yes, I do want the sum of the two columns...

I'd say you were an MVP again, but that didn't quite work...

It was giving me a value that wasn't quite correct. So, I compared it side by side with the formula I was using for the sum of the single column and noticed you had swapped the dates and the greater than/less than signs a bit. Not really sure why it mattered (as I'm dumb about all this, as you can tell by now), but I tried putting them back to the way they were in the single column summation. It worked...it's now giving me the correct sum.

Here's the new formula in case someone else needs it for their project...

=SUMPRODUCT(--('Load Entry'!$C$4:$C$563<=$A$28),--('Load Entry'!$C$4:$C$563>=$A$27),'Load Entry'!$S$4:$S$563+'Load Entry'!$U$4:$U$563)

For reference: C4-C563 are the columns that contain the dates to search through. A27 and A28 are the start and end date parameters. S and U are the columns to sum together if they fall within the dates specified.

Thanks Valko. Okay, you really are an MVP. I would have never figured all this out today that you have helped me with. I really appreciate it.

The only difference in our formulas is that I test for the lower date boundary first, then the upper date boundary where you're testing the upper date boundary first then the lower date boundary.

The net effect can be expressed in a well known saying: six of one, half dozen of the other!

Thanks for the feedback!