[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

=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.
0
jodyreid
Asked:
jodyreid
  • 8
  • 4
1 Solution
 
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
 
jodyreidAuthor 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
 
mark_harris231Commented:
Sorry - thought I confirmed was attached.  Try this...
18-MonthSales.xlsx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
jodyreidAuthor 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
 
jodyreidAuthor 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
 
mark_harris231Commented:
See here for more info/screenshots on filtering for unique records:

Filter Unique
0
 
jodyreidAuthor Commented:
Thank you. It worked out great.
0
 
mark_harris231Commented:
Glad to hear.  Thanks, Mark
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now