# SUMPRODUCT Problem with blank non blank cells included in range and ever changing range

1. ## SUMPRODUCT Problem with blank non blank cells included in range and ever changing range

Hi

I have two queries with a SUMPRODUCT formula I'm trying to write:

1st - to calculate a range but I keep running into trouble due to cells that appear to be blank but are not (demonstrated in the example in cell A7 with a ') and returns a #VALUE. - I have tried a number of things ISBLANK, COUNTA, COUNTBLANK with no joy. SUMPRODUCT formula in Cell D1.

2nd - this range will vary in column length each time and i need it a simply addition to the SUMPRODUCT formula to take account of this and go down to the last cell with an actual number in column A. Hope i have explained this properly.

Obviously, report churns out the date in column A as text and it is usually more than the 7 lines i have entered.

Any help would be great.  Register To Reply

2. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

Try this out:

=SUMPRODUCT(IFERROR(VALUE(A1:A7)<40000,0)*IFERROR(VALUE(A1:A7)>20000,0),B1:B7)

you will need to run as an array (Ctrl+Shift+Enter)

im not sure i understand your second part though.  Register To Reply

3. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

You could try using an "array formula", i.e.

=SUM(IF(ISNUMBER(A1:A7+0), IF(A1:A7+0>20000,IF(A1:A7+0<40000,B1:B7))))

confirmed with CTRL+SHIFT+ENTER  Register To Reply

4. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

I've tried both and neither comeback with anything. Also, not sure what you both mean with 'confirmed with CTRL+SHIFT+ENTER'.

DGagnon - the second part means that at the moment the range is 6 lines, the next time it could be 20, 30, even 200 lines. I just need it to go to the last cell with actual figures in. with the attachment example, that would be line 6 even though line 7 has blank items in it. Hope this explains a little better.  Register To Reply

5. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang Originally Posted by argegg26 .....not sure what you both mean with 'confirmed with CTRL+SHIFT+ENTER'......
This is essential for array formulas. Put the formula in a cell, select that cell and press F2 key to select formula.....then hold down CTRL and SHIFT keys and press ENTER. If done correctly then curly braces like { and } will appear around the formula.

You can extend the ranges to accommodate any expansion, it doesn't matter if some rows are blank, e.g. try

=SUM(IF(ISNUMBER(A1:A1000+0), IF(A1:A1000+0>20000,IF(A1:A1000+0<40000,B1:B1000))))  Register To Reply

6. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

Like i do with most of my posts, I like to learn these things, why is the f2 and ctrl+shift+enter so vital with an array formulas? also, what is the difference between a normal formula and an array formula?
Sorry to ask, just so i know so i can apply it in the future.  Register To Reply

7. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

an Array formula will run multipul calculations together, for example, in a standard formula =IF(A1:A2=2,1,0) it will only return one result either a 1 or a 0, with an array formula it will return an array of results {1,1} or {1,0} or {0,1} or {0,0} for example depending on what was entered

using the above formula if A1 = 3 and A2=2 then the array would return {0,1} because it is true only in the second case, where as a standard formula would only look to the first case, and return a 0.

you could then do something like a sum on that, which would add the results together, or an average even.

the array formula of =AVERAGE(IF(A1:A2=2,1,0)) would return .5 in the case above.

hope this helps.  Register To Reply

8. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

Thanks again DGagnon.

Would I be right in thinking I could enter the { and } when writing the formula instead of f2, then Ctrl+Shift+enter?  Register To Reply

9. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

No I wouldn't it would appear  Register To Reply

10. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

no, you must use ctrl+Shift+Enter, using the { } wont do anything, it will actualy turn your formula to a text string.  Register To Reply

11. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

And another quick question, why is the +0 so important in the formula? as when I remove it, it does not work and I can't understand why removing 0 would have this effect.  Register To Reply

12. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

it converts the text values returned into #'s which will allow mathmatical comparisons such as > and <  Register To Reply

13. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

so it's a quicker way of using VALUE(...) formula?  Register To Reply

14. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

basicaly, yes.  Register To Reply

15. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

Once again, Thanks DG.  Register To Reply

16. ## Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

Another quick question. Formulas in columns A and B, ie to return a number between 0 and 40000 and the value in column B. I have tried the above CSE formula with formulas in the ranges and it come back #value. Do you know how I amend/address this?  Register To Reply