# 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.