SumIf vs Pivot Table

SumIf vs Pivot Table

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.

Both provide the answer Raj is looking for. Want to see more? Download the example here.

Leave a Reply

Your email address will not be published. Required fields are marked *