CODEWITHSUNDEEP
excelexcel-formula
A Jones
A Jones

Reputation: 3

How to find the sum for customer's multiple payments using Excel formula?

Hello,
I am trying to configure a =if(countif) code in Excel to calculate the sum for customers of multiple payments. I'm basing their unique value on their Customer# as that's the most unique identifier as some people might have the same name. The code I've entered is having some issues and I don't have a strong experience with using this type of formula. Below is a generic sample and the code.

A:Name      B:Customer# C:Paid  D: Sum
Jane Doe    1044        88
Jane Doe    1044        22
Jack Doe    1088        6
Jack Doe    1088        5
Jane Doe    1520        200
Katey Doe   1222        65
Katey Doe   1222        4
Jack Doe    1045        6
Jack Doe    1045        78
Bill Doe    1011        5

(I'm unable to post an image yet)

This is the code I'm trying to use in column D: =IF(COUNTIF(B:B,B1)>1,IF(COUNTIF(B$:B1,B1)=1,=SUM(C:C,C1),=SUM(C:C,C1))"")

Upvotes: 0

Views: 836

Answers (2)

urdearboy
urdearboy

Reputation: 14580

SUMIF

On cell D2 enter the formula =SUMIF($B$2:$B$11,B2,$C$2:$C$11) and drag down which will create this table

enter image description here

Pivot Table

A pivot table would be my summary method. This will stop you from having repeated values. Notice that the Sum for each customer is repeated which is not the ideal view. Instead you can try a simple pivot that only requires 3 columns: Name, CSR #, & Paid

enter image description here

Upvotes: 3

JvdV
JvdV

Reputation: 75840

Seems like you want to use SUMIF()

enter image description here

Formula in D2:

=SUMIF($B$2:$B$11,B2,$C$2:$C$11)

Drag down..

Upvotes: 2

Related Questions