# Sum if??

Posted by Steven on December 20, 2001 9:32 AM

I have data in one field and I want to tally sales by instances of the code in the same row ie I want spend totals for Codes There are 6000 records in the file so a coded answer would help or a way of tallying in pivot table

Code Name Spend
111 Ste 12
111 Ste 13
222 John 25
222 John 32

Posted by Mark O'Brien on December 20, 2001 9:35 AM

Sounds like a job for an Array Formula (sorry a CSE formula). Have a look at this example, it sounds exactly liek what you're looking for.

Posted by Mark O'Brien on December 20, 2001 9:49 AM

OOPS

Also, look up SUMIF function on Excel help.

Posted by Aladin Akyurek on December 20, 2001 11:12 AM

Steven --

Either use:

(a) PivotTables (as you suggest yourself) where you can have Name as Page Field, Code as Row Field, and Spend as Data Field.

(b) a SUMIF formula that totals spendings per Code:

Assuming that A1:C5 houses your sample data:

activate A2,
Enter as Input range A1:A5,
Check Copy to another location,
Enter as value for Copy To F2,
check unique records only,
and click OK.

You'll get in F2:F5

{"Code";
111;
222;
333}

In G3 enter: =SUMIF(\$A\$2:\$A\$5,F3,\$C\$2:\$C\$5)

Copy this as far as needed.