SUMIF Question

Joined
Feb 14, 2007
Messages
5
Reaction score
0
I have a simple table, column A is sale dates, and column B is the amounts. Column A has about 5 years worth of data (extracted from a proprietary system) and I want to sum the sales for each month (via reference cell) and year.

I had thought it would be something like:
=SUMIF(A:A,(MONTH(C1),YEAR(C1)),B:B)
But that doesn't work.

Other than a pivot table, what is the way to do this?

Cheers
 
Joined
Dec 28, 2006
Messages
23
Reaction score
0
here's a solution. Say you have dates in A3:A300 and data at B3:B300.

Make a table of months by number, say at C3:C15 with the numbers of 1 through 12. In cell D3, type the following formula:

=sum((month($a$3:$a$300)=c3)*($b$3:$b$300))
This is an array function, so when you are finished entering the formula, you need to hit ctrl-shft-enter. This will evaluate each date to see if it is a January (month 1) and return a "FALSE" or "TRUE" statement. It will then convert the TRUE's to the number 1, and the FALSE's to the number zero, and multiply the resulting table of 1's and 0's by the corresponding entries of data in column B, then sum all of the resulting multiplications. Voila ... the sum of all January data! Copying the formula down from cell D3 to D15 will complete the profile for all months. Don't forget the ctrl-shft-enter step as you need it to get the array function to work.

To do the same for years, make a column of calendar years, and then duplicate the above procedure replacing the "month" function with "year". To test yourself, sum all of the months and compare it to the sum of all of the years. They should be the same.

Steve
 
Joined
Feb 14, 2007
Messages
5
Reaction score
0
Thanks Steve

Not quite what I was after, I need to sum all the sales for each month in each year. Output needs to look somthing like:
Month Sales
Jan-05 $512.12
Feb-05 $1028.03
....

So I basically want (in words) sum B:B if A:A matches the month and year of C3.

Hope you can help because this is sending my spare
wallbash.gif
 
Last edited:
Joined
Dec 28, 2006
Messages
23
Reaction score
0
Ok ... here you go. You have dates in column A, and data in column B, say starting at A3 and B3. Make sure they are sorted in increasing order of date. At cell C3, enter the number 1. At cell C4, enter this formula:

=if(or(month(A4)>month(A3),year(A4)>A3),1+C3,C3)
Copy this formula down the column for as long as your data. This will create a list of unique month-numbers. Say the first two dates are in January 2003 and the third date is in February 2003. C3 and C4 will equal 1,and C5 will equal 2. If your last month is Dec 2006, the last entry in colum C should be 48 as the 48th month in the data set. Lets say you have 100 data points, so your last entry should be in row 102.

Now create a column of months/years you want to summarize. Say, enter 1/1/03 if that is your first month at cell G3, followed by 2/1/03 at G4. AutoFill these two down for as long as you have months to analyze. You can date format that column so only "Jan-03" etc shows up. You should end up with 48 entries, matching the 48 months of your data.

To the right of these month entries, enter 1, 2, 3, etc at H3, H4, etc. to number the months of your data. This total should match the final number determined above (48, in my example).

Now, finally, to the right of the first month number, at I3, enter:
=sum(($C$3:$C$102=H3)*($B$3:$B$102))
Again, use ctrl-shft-enter to enter this formula, and copy it down the length of column I. You should get your answers.

Seems rather inelegant, but I think it works.
 
Last edited:
Joined
Feb 14, 2007
Messages
5
Reaction score
0
Thanks again Steve

Been tinkering and have worked it out, maybe a little more elegantly ;)
I used this in D3:
=SUMIF(A:A,"<="&(DATE(YEAR($C3),MONTH($C3)+1,0)),B:B)-SUMIF(A:A,"<"&$C3,B:B)

Where C3 = 01-Jan-2003

Works perfectly.

Again thanks, your intial reply triggered off a thought that lead me to this!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top