Need a formula that will sum the value of dollars within a given date range

[twalker123]

Hi,

In the attached Excel file, I've created a dummy example of my master data.

Each ID is a location. At each location, there is a dollar amount for each date. For this exercise, I already know the first and last dates in columns H and I. Using those dates, I'd like to return in column J the sum value of the dollar amounts in the known date range. Is this possible?

Thank you in advance.

[Vraag en antwoord]

Try this.

Please Login or Register  to view this content.

[alansidman]

In J2 =SUMIFS(B2:G2,$B$1:$G$1,">=" &H2,$B$1:$G$1,"<=" &I2) and copy down

[twalker123]

Both of these solutions worked perfectly in my sample file, but when I tried to implement them into the main file the calculation returned a value of zero, which isn't correct. I can't seem to figure out what I'm missing. In the main file, the date range is in columns C through S, and the first/last dates are entered in columns T and U. This is what I updated the formula to when I put it into my master data sheet:

=SUMPRODUCT(($C$1:$S$1>=T2)*($C$1:$S$1<=U2)*(C2:S2))

And this is the other option:

=SUMIFS(C2:S2,$C$1:$S$1,">=" &T2,$C$1:$S$1,"<=" &U2)

Both are returning a sum of "$0.00" in my results column V for some reason.

[FDibbins]

a few things to check...
1. Are your dates real dates or text looking like dates?
2. are the values you need to sum, really numbers or text looking like numbers?

for both of these, test with =isnumber (cell-ref) FALSE indicates text and we will need to convert them

Where is the data coming from? If it is being imported, it is probably being imported as text

[twalker123]

Yep, that was it! The dates were stored as text, after I converted them to values it was no problem. Both of these formulas worked for me, thank you!

[FDibbins]

Fairly common problem. Happy to help