how to extend sumproduct formula beyond the current range

**Gerti Ballia** · 10-22-2022, 11:27 AM

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!

**JohnTopley** · 10-22-2022, 11:40 AM

Try

=SUMPRODUCT(((LEFT(A4:A600,1)="1")+(LEFT(A4:A600,1)="2")),(B4:B600))

**Gerti Ballia** · 10-22-2022, 11:48 AM

it works at first, but when I delete rows from the end, the amount stays unchanged. However, when I delete cells or rows in the middle, the result is updated. Very strange!

**Glenn Kennedy** · 10-22-2022, 11:41 AM

The formula doesn't like the blank cells. It falls over, giving a #VALUE error.

**ExceLogan** · 10-22-2022, 11:42 AM

Hi there,

Your formula has a problem when you try to extend the ranges, because the empthy espaces generate errors.

I suggest this alternative solution:

=SUMAPRODUCT(MMULT(--(LEFT(A4:A600)={"1"\"2"});{1;1});B4:B600)

I hope was usefull.

**Gerti Ballia** · 10-22-2022, 11:53 AM

Hi Logan, thanks but the formula does not work correctly. It is something related to brackets...

**ExceLogan** · 10-22-2022, 11:59 AM

Hi again, maybe the problem was the argument separator ";", try change it all by ","

**Sam Capricci** · 10-22-2022, 11:45 AM

if I put data in past row 600 it returns results
it is returning a value error because it is looking for data that isn't there.
see in the example.

**Gerti Ballia** · 10-22-2022, 11:55 AM

Yes you are right, but how do I make that it does not give #value. I do not want to change the formula anytime I change the data set

**Glenn Kennedy** · 10-22-2022, 11:48 AM

One way round this is to cretae two Named Ranges (CTRL-F3 to edit):

=$A$4:INDEX($A:$A,MATCH(10^100,$A:$A))

and

=$B$4:INDEX($B:$B,MATCH(10^100,$B:$B))

called Acc and Amt. These adst to cover your exact range, automatically.

Then use something like:
=SUMPRODUCT((--((LEFT(Acc)+0=1)+(LEFT(Acc)+0)=2))*Amt)

**josephteh** · 10-22-2022, 12:02 PM

If the Account Codes all consist of 7 digits, why not just use SUMIFS: =SUMIFS(B4:B600,A4:A600,">=1000000",A4:A600,"<3000000").

**Glenn Kennedy** · 10-22-2022, 12:08 PM

Did you look at Post 6??

**Gerti Ballia** · 10-22-2022, 12:11 PM

hello, I checked and it worked. Thank you so much for your support1

**Glenn Kennedy** · 10-22-2022, 12:16 PM

You're welcome.

**Sam Capricci** · 10-22-2022, 12:16 PM

well, I was going to mention that if you are not averse to using a helper column there is a simple formula and fix...
in col C... =IF(A4="","",IF(OR(--LEFT(A4,1)=1,--LEFT(A4,1)=2),"yes",""))
then this sumif =SUMIF(C:C,"yes",B:B)
that covers the entire range regardless of the number of cells you have data in.

how to extend sumproduct formula beyond the current range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Re: how to extend sumproduct formula beyond the current range

Thread Information

Users Browsing this Thread

Similar Threads

Extend formula - for a range of columns

[SOLVED] Improved Formula / i've run out room to extend the current formula

[SOLVED] Improved Formula / i've run out room to extend the current formula / (avdanced code needed

Extend formula range dinamically while using an array ?

[SOLVED] Extend current formula for a date range

Using SUMPRODUCT based on current date range

Extend Summation Range in SUMPRODUCT

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions