Sales sheet- advanced if/sumif question

**cpcg32** · 08-07-2009, 02:24 PM

I have two worksheets that I am creating, one is a Sales sheet and the other is a Contact list. The Sales sheet will have a seperate row for each item a customer buys. In the Contact sheet, for each customer, I would like to have a total of his purchases. What formula can I use to make the Contact list automatically total for each individual from the Sales sheet?

Attached is an image of the two sheets.

**DonkeyOte** · 08-07-2009, 02:27 PM

Answer will in part depend on the version of XL you're running... XL2007 or earlier ? You might also want to think of using a Pivot Table in preference to Formule...

**cpcg32** · 08-07-2009, 02:47 PM

Running 2007

**DonkeyOte** · 08-07-2009, 03:29 PM

Can you elaborate on your expected results - not sure I understand the significance of the Y/N columns on your image... best normally to post a sample file.

In general though given your use of XL2007 check out XL Help on the SUMIFS function (and possibly COUNTIFS pending other requirements we're not aware of).

**cpcg32** · 08-07-2009, 04:10 PM

You can ignore the Y/N columns.

Using a pivot table solves half of my probably, getting the sum for each individual. How do I take the next step of saying "if this email equals an email in this range, put in the value that corresponds with that email"?

Attached is the file, with the pivot table

**DonkeyOte** · 08-07-2009, 04:22 PM

Not sure I understand that totally.. in terms of formulae, if you're note differentiating by anything other than C (email) then you can use a standard SUMIF, eg:

Contact!D5: =SUMIF(Sales!$C:$C,$C6,Sales!$F:$F)
copied down

If you want to add further criteria you can (in XL2007) use SUMIFS

I'm going offline now but others will help out if needed.