=sumif in excel

I am looking to find the total a customer has spent over the last 18 months. I have a spreed sheet with all my customers and each product the got and the cost of these items. What i am looking to do is setup an =sumif() to add all the products for the last 18 months and return a total for each customer. is this possible. Thank you.
jodyreidIT ManagerAsked:
Who is Participating?
 
mark_harris231Commented:
See here for more info/screenshots on filtering for unique records:

Filter Unique
0
 
mark_harris231Commented:
Couple of different ways to go with this.  Attached is an example using SUMIFS (not SUMIF).

I included a couple of different formulas - one in G2-I2, which looks at the cutoff date in L2, and one in G4-I4, which builds the date calculation into the formula.  Both methods use the following formula to determine the 18-month date cutoff: =TODAY()-540  (18 months * 30 days = 540).  You can play with the date formulas as desired.
0
 
jodyreidIT ManagerAuthor Commented:
Sorry Mark but I did not get the attachments. Would love to see your salutation to my problem as I think you have got what I need. Please send again. Thank you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mark_harris231Commented:
Sorry - thought I confirmed was attached.  Try this...
18-MonthSales.xlsx
0
 
mark_harris231Commented:
Just to help a bit further with the SUMIFS formula in my example:

=SUMIFS($C$2:$C$10,$A$2:$A$10,G$1,$D$2:$D$10,">"&TODAY()-540)

$C$2:$C$10 = the range to be summed

$A$2:$A$10 = the first criteria range to check

G$1 = the condition to check against the first criteria range.  In this case, we're checking to make sure that the customer name in the header row is found in column A range.

$D$2:$D$10 = the second critera range to check

">"&TODAY()-540 = the condition to check against the second criterial range.  It evaluates each date in the Column D range for dates greater than today's date -540 days.

Hopefully this gets you started down the right path
0
 
mark_harris231Commented:
Also, pay close attention to the use of absolute vs. relative referencing in the formulas.  If this is a recurring/on-going use spreadsheet, you might want to look at the use of dynamic named ranges in place of absolute range references.

Have a look at the following link for more discussion on dynamic named ranges:

Dynamic Named Ranges
0
 
jodyreidIT ManagerAuthor Commented:
This is working fine but i have to keep changing the $A$2 for each customer. Is there away it can work with out me having to manuly changing this. I have over 30,000 customers and about 80,000 products so it will take me forever. I will upload a sample of what i have done. Thanks
0
 
mark_harris231Commented:
A sample would be ideal.  Just make sure it is "sanitized" (i.e., no real customer data)
0
 
jodyreidIT ManagerAuthor Commented:
Here is the data. Thank you.
Cust.xls
0
 
mark_harris231Commented:
OK...I see the issue.  You're trying to show the 18-months purchases on the first row of each customer.  My suggestion would be to create a summary table that only contains a list of unique customer IDs.  You can do this by selecting Column A, selecting the Data tab,  Filter Advanced > Copy to another location > Copy to: G1, and select Unique records only.

Then put this 18-month calculation in H2 and copy down as far as needed:

=SUMIFS($C:$C,$A:$A,$G2,$B:$B,">"&-540)

This will give you a more condensed list of customer accounts to analyze (vs. requiring a scroll through the entire 80,000+ sales records).
Copy-of-Cust.xlsx
0
 
jodyreidIT ManagerAuthor Commented:
Thank you. It worked out great.
0
 
mark_harris231Commented:
Glad to hear.  Thanks, Mark
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.