Sumproduct between range

[jharvey87]

Hello everyone,

I have a query producing data and unfortunately our sub-ledger units appear to cause some issues with Excel. When the query produces into excel, it comes with a space in front of it, for example " 6102300". I'm in need of a formula that can sum between two ranges while somehow ignoring this space. I've tried converting to number, text, general and not having much luck with a sumif or sumproduct function. In the attached example, I'm trying to sum amounts from column D between 6102000 and 6103999. Can someone let me know what I'm doing wrong?

Thanks for the help.

[Glenn Kennedy]

4000 rows makes manual checking a little difficult. I cut it back to a real SAMPLE, 15 rows.

1. Replace values in I4 and J4 with numbers.

2. Select column E. Data/Text to columns/Finish.

3. =SUMPRODUCT((E4:E20>=I4)*(E4:E20<=J4),D4:D20) now works.

[Glenn Kennedy]

Ignore Post 2. Back in a minute!!

[Glenn Kennedy]

OK. minor error in description.

4000 rows makes manual checking a little difficult. I cut it back to a real SAMPLE, 15 rows.

1. Replace values in I4 and J4 with numbers.

2. Add anything to E1 and E2. Select column E. Data/Text to columns/Finish. Deletet whatever was in E1 and E2.... or...
Select E4 to the end instead.

3. =SUMPRODUCT((E4:E20>=I4)*(E4:E20<=J4),D4:D20) now works.

[jharvey87]

Appreciate it, but not sure this will work. I need to analyze the full range and when I extend the formula, its not pulling anything. I can't control the data in column E in terms of the spacing it puts in front of the number. If I manually type in the number, then yes the formula works. Is there anyway to address the spacing issue within the formula?

[jharvey87]

Actually I apologize, your formula is working. Thank you!

[Glenn Kennedy]

Works fine for me, if you follow the instructions in Post 4. It produces a number. I have not done any manual checking!!

[jharvey87]

Appears everytime i refresh the query, it goes back to showing 0.00. Would a sumproduct function solve this issue?

[davsth]

whats to stop a global replace of space with nothing at the beginning?, then they all become numbers and it should work?

[jharvey87]

Can you clarify would you mean here:
Add anything to E1 and E2. Select column E. Data/Text to columns/Finish. Deletet whatever was in E1 and E2.... or...
Select E4 to the end instead.

[Glenn Kennedy]

If this is something you're constantly refreshing, go with davsth's idea. use something like:

=IFERROR(TRIM(E4)+0,"")

in G4 copied down. Reformat I4 and J4 as numbers


=SUMPRODUCT((G4:G6000>=I4)*(G4:G6000<=J4),D4:D6000)