Adding values for all rows in a range
 

My apologies if this question has been asked before.

I have a list of vendors and amounts paid for each of their invoices.
I want to automatically add the payments to each vendor.

TOTAL
VENDOR INVOICE PAID PAID
A 100 4526
A 250 4625
A 578 4626 928
B 10 SC17
B 186 SC350 196
C 541 Inv 56
C 325 Inv 78
C 18 Inv 102 884

What formula would do that without having to go and write a standard
Sum formula after every change of vendor?

Many thanks

Adding values for all rows in a range
 

Hi Alvaro,

Am Thu, 6 Oct 2011 13:05:04 -0700 (PDT) schrieb Alvaro:

TOTAL
VENDOR INVOICE PAID PAID
A 100 4526
A 250 4625
A 578 4626 928
B 10 SC17
B 186 SC350 196
C 541 Inv 56
C 325 Inv 78
C 18 Inv 102 884

in D3:
=IF(A4=A3,"",SUMPRODUCT(--($A$3:$A$1000=A3),--($B$3:$B$1000)))
and fill down


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Adding values for all rows in a range
 

Thank you very much for your effort, Klaus.

I copied your formula all the way down in col D and it was effective in detecting the change in the name of the vendor, however it gave the "#VALUE!" every time as the answer.

I also noticed that the sumproduct part of the formula will never capture the values in col C, 100+250+578=928 for vendor A of the sample) which is what I need. It may be because the formatting of my message was altered.

Adding values for all rows in a range
 

Hallo Alvaro,

Am Thu, 6 Oct 2011 18:40:35 -0700 (PDT) schrieb :

I copied your formula all the way down in col D and it was effective in detecting the change in the name of the vendor, however it gave the "#VALUE!" every time as the answer.

look he
https://skydrive.live.com/view.aspx?...182 2A3%21197


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Adding values for all rows in a range
 

Thanks again Claus.

I did look at the suggested URL and it shows the formatted sample, as I sent it.

Would it be possible for you to please write the formulas in col D, so that the 928, 196 and 884 are calculated automatically by adding the values in col B for every vendor.

Thank you very much for your interest in helping.

Alvaro

Adding values for all rows in a range
 

Hi Alvaro,

Am Fri, 7 Oct 2011 06:36:05 -0700 (PDT) schrieb :

I did look at the suggested URL and it shows the formatted sample, as I sent it.

Would it be possible for you to please write the formulas in col D, so that the 928, 196 and 884 are calculated automatically by adding the values in col B for every vendor.

it's not your sample - there are formulas in col D. You have to download
the sample to see it.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Adding values for all rows in a range
 

Sorry Claus,

I did it on the sample and it did work. I should have done this before I sent you my previous message.

Now I am going to translate it to the worksheet that I need.

Thanks a lot once more.

Alvaro

Adding values for all rows in a range
 

Hi Alvaro,

Am Fri, 7 Oct 2011 06:42:09 -0700 (PDT) schrieb :

Now I am going to translate it to the worksheet that I need.

if you download the sample, the formula will translate automatically


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2