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
Any help gladly received
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,
activate Data|Filter|Advanced Filter,
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.
Aladin
==========
