# =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.
###### Who is Participating?

Commented:

Filter Unique
0

Commented:
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

IT 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

Commented:
Sorry - thought I confirmed was attached.  Try this...
18-MonthSales.xlsx
0

Commented:
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

Commented:
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

IT 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

Commented:
A sample would be ideal.  Just make sure it is "sanitized" (i.e., no real customer data)
0

IT ManagerAuthor Commented:
Here is the data. Thank you.
Cust.xls
0

Commented:
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

IT ManagerAuthor Commented:
Thank you. It worked out great.
0

Commented: