Solved

# =sumif in excel

Posted on 2012-08-16
306 Views
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
Question by:jodyreid

LVL 10

Expert Comment

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

Author Comment

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

LVL 10

Expert Comment

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

LVL 10

Expert Comment

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

LVL 10

Expert Comment

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

Author Comment

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

LVL 10

Expert Comment

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

Author Comment

Here is the data. Thank you.
Cust.xls
0

LVL 10

Expert Comment

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

LVL 10

Accepted Solution

Filter Unique
0

Author Closing Comment

Thank you. It worked out great.
0

LVL 10

Expert Comment

0

## Featured Post

### Suggested Solutions

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…