how to extend sumproduct formula beyond the current range

[Gerti Ballia]

Hello All,

I am using the sumproduct to sumif when the first number is 1 or 2. in column A I have the list of the accounts which are 7 digit number and in the column B, I have the amount. The formula is SUMPRODUCT(--(VALUE(LEFT(A4:A60,1))=1)+(VALUE(LEFT(A4:A60,1))=2),(B4:B60)). It works fine, but the issue is that I need to use the same formula for different range when pasting different set of data. When I try to extend the range of data and changing the formula in SUMPRODUCT(--(VALUE(LEFT(A4:A600,1))=1)+(VALUE(LEFT(A4:A600,1))=2),(B4:B600)), then I get #VALUE!. I want the formula to work whenever I paste different set of data , like 20 or 200 rows in the column A and B.

Thank you!

 

[JoeMo]

Perhaps one or more of the entries in column A of the larger data set is not a a number greater than or equal to zero. That will cause your extended formula to return #VALUE!.

 

[Gerti Ballia]

Hello, thanks for the replying. The photo shows how my data is. After row 60, all I have is blank.

Perhaps one or more of the entries in column A of the larger data set is not a a number greater than or equal to zero. That will cause your extended formula to return #VALUE!.

 

[JoeMo]

Hello, thanks for the replying. The photo shows how my data is. After row 60, all I have is blank.

Well, a blank cell is not a a number greater than or equal to zero. So, that's causing the #VALUE! error. How about trying this:

=SUM(IF((A4:A600>=0)*((A4:A600=1)+(A4:A600=2)),B4:B600,0))

Depending on what version of Excel you are using, you may have to confirm this formula using ctrl+shift+enter.
This formula will not return an error if you have one or more col A entries in range A4:A600 that are blank.