Earlier this week I received an email from Raj. He was new
to Excel and had a problem he could not figure out. Raj had a list of accounts
and was trying to get the total quantity of purchase orders and the total value
of the purchase orders for each account. The problem he was having was that
there were multiple lines for each account (based on the fiscal month). Here is
a sample the data he had.
A
|
B
|
C
|
D
|
|
1
|
Account Number
|
Fiscal Month
|
Number Of Purchase Orders
|
Total PO Value
|
2
|
1000002
|
Jan
|
3
|
1864
|
3
|
1000004
|
Jan
|
5
|
3232
|
4
|
1000009
|
Jan
|
4
|
4469
|
5
|
1000004
|
Feb
|
1
|
3368
|
6
|
1000007
|
Jan
|
6
|
3675
|
7
|
1000009
|
Feb
|
2
|
2953
|
8
|
1000005
|
Jan
|
4
|
5994
|
9
|
1000009
|
Mar
|
1
|
4812
|
10
|
1000001
|
Jan
|
3
|
5221
|
11
|
1000008
|
Jan
|
4
|
5126
|
12
|
1000007
|
Feb
|
2
|
5812
|
13
|
1000002
|
Feb
|
5
|
3334
|
14
|
1000005
|
Feb
|
4
|
4398
|
15
|
1000008
|
Feb
|
4
|
2948
|
16
|
1000005
|
Mar
|
1
|
2184
|
17
|
1000002
|
Mar
|
6
|
3670
|
I have highlighted the accounts above to help show the
multiple rows.
Well there are a few ways to solve this problem. To start I want to have a column with just a
list unique accounts (no duplicates).
F
|
Account Number
|
1000001
|
1000002
|
1000004
|
1000005
|
1000007
|
1000008
|
1000009
|
I then can use the SUMIF formula to solve Raj’s problem.
F
|
G
|
H
|
I
|
J
|
Account Number
|
PO Order Total
|
formula
|
PO Total Value
|
formula
|
1000001
|
3
|
=SUMIF(A:A,F7,C:C)
|
5,221.00
|
=SUMIF(A:A,F7,D:D)
|
1000002
|
14
|
=SUMIF(A:A,F2,C:C)
|
8,868.00
|
=SUMIF(A:A,F2,D:D)
|
1000004
|
6
|
=SUMIF(A:A,F3,C:C)
|
6,600.00
|
=SUMIF(A:A,F3,D:D)
|
1000005
|
9
|
=SUMIF(A:A,F6,C:C)
|
12,576.00
|
=SUMIF(A:A,F6,D:D)
|
1000007
|
8
|
=SUMIF(A:A,F5,C:C)
|
9,487.00
|
=SUMIF(A:A,F5,D:D)
|
1000008
|
8
|
=SUMIF(A:A,F8,C:C)
|
8,074.00
|
=SUMIF(A:A,F8,D:D)
|
1000009
|
7
|
=SUMIF(A:A,F4,C:C)
|
12,234.00
|
=SUMIF(A:A,F4,D:D)
|
55
|
63,060.00
|
So what is this formula doing? For the PO Total column the
sumif formula takes the account number in column F and then looking in column A
for the match. It then sums all the values in column C that have the match.
Now I could also
create a simple pivot table to solve Raj’s problem.
No comments:
Post a Comment