Total Paid Amts Per Account Number 2

[JcTon]

Need to solve a problem.

Have an Excel worksheet with the following columns;

AccountNo-----PaidAmt
12463---------100
12463---------200
12463---------300
25466---------250
25466---------175

How can I sum up the total paid amount by Account No using a sumproduct formula?

I am aware of using Excel subtotal but wanted to know how I can use sumproduct formula.

 

[AnotherHiggins]

=SUMPRODUCT((A1:A1000=A1)*(B1:B1000))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I should have stipulated:
[tab]=SUMPRODUCT((A1:A1000=A1)*(B1:B1000))
where A1 contains the account number you're after. You could also use
[tab]=SUMPRODUCT((A1:A1000=12463)*(B1:B1000))
Or have the list of account numbers on one sheet and the data on a second sheet, then have the formula refer to the second sheet:
[tab]=SUMPRODUCT((Sheet2!A1:A1000=Sheet1!A1)*(Sheet2!B1:B1000))


Keep in mind that each section of the SumProduct formula must contain the same number of rows and that you cannot reference an entire column (A:A won't work for SumProduct, but A1:A65535 will)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JcTon]

Actually, I am not picking up the correct amount using the formula provided.

I currently have something like "=sumproduct((Account=A2)*(Paid))

Did try your formula as well.

The result desired, using the example provided, is for
$600 to be in the "sumproduct column" for Account 12463 and $425 to be in the "sumproduct column" for Account 25466.

In other words;

AccountNo-----PaidAmt---SumProductCol
12463---------100-------$600
12463---------200-------$600
12463---------300-------$600
25466---------250-------$425
25466---------175-------$425

 

[AnotherHiggins]

Ah - to drag the formula down, you'll want to use Absolute References for the ranges.

Absolute Reference is indicated by "$"s in the cell reference.

[tab]=SUMPRODUCT(($A$1:$A$1000=A1)*($B$1:$B$1000))

If you select A1:A1000 then press [F4] several times, you will see that it cycles through different reference styles.
[tt]
A1:A1000 Relative Reference
$A$1:$A$1000 Absolute Reference
A$1:A$1000 Relative Columns, Absolute Rows
$A1:$A1000 Absolute Columns, Relative Rows
[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JcTon]

Well,

Receiving 0s in the "sumproduct column"

Any further suggestions?

 

[onedtent]

apologies for jumping in. it should be $A$2 and $B$2 and not $A$1/$B$1 in the formula


- onedtent OneDTenT One D Ten T (1D10T)
Not a complete idiot. Some parts are still missing.