- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Gurus, Converge!
Posted on 3/5/15 at 10:09 am
Posted on 3/5/15 at 10:09 am
I need a formula to sum all entries in row "Y" where the value in row "X" begins with "z". Can this be done? The value in row "x" is a date in the format of 3/5/2015, if that matters. Basically, I'm trying to sum all data for the specified month.
Posted on 3/5/15 at 10:18 am to h0bnail
You can use SUMIF or SUMIFS with a wildcard * or ? as place holders. So somthing like:
=SUMIF(range, criteria [, sum_range])
=SUMIF(X1:X99,"z*",Y1:Y99)
=SUMIF(range, criteria [, sum_range])
=SUMIF(X1:X99,"z*",Y1:Y99)
Posted on 3/5/15 at 10:24 am to h0bnail
Look up SUMPRODUCT. This example sums for January.
=SUMPRODUCT((MONTH(X1:X2)=1)*Y1:Y2)
=SUMPRODUCT((MONTH(X1:X2)=1)*Y1:Y2)
Posted on 3/5/15 at 12:10 pm to jeekers78
SumIFs will work, but you'll have to convert the date range to Excel Date code. The wildcard won't work in that format difference.
If you have two other cells you can use to define the date range, it'll be easy. Cell A is lowest date, Cell B is higher date.
= SumIfs(Y1:Y99, X1:X99, ">="&Cell A, X1:X99, "<="&Cell B)
ETA: there's some assumptions, mainly that the dates are actual date format, and not text. If text, the wildcard will work and SumIf only required (assuming you only want to sum the month without multiple years and no other functionality required).
If you have two other cells you can use to define the date range, it'll be easy. Cell A is lowest date, Cell B is higher date.
= SumIfs(Y1:Y99, X1:X99, ">="&Cell A, X1:X99, "<="&Cell B)
ETA: there's some assumptions, mainly that the dates are actual date format, and not text. If text, the wildcard will work and SumIf only required (assuming you only want to sum the month without multiple years and no other functionality required).
This post was edited on 3/5/15 at 12:21 pm
Posted on 3/5/15 at 1:26 pm to LSUtigerME
Yes, the cells are formatted as dates. If it would simplify things, I could stick a row at the top with the month name. It would eliminate the need for using a wildcard.
Posted on 3/5/15 at 2:36 pm to h0bnail
You could always create another row with
=month(x1)
And drag it. Then do
=sumifs(y1:y5000,aa1:aa5000,z)
=month(x1)
And drag it. Then do
=sumifs(y1:y5000,aa1:aa5000,z)
Posted on 3/5/15 at 2:37 pm to h0bnail
quote:
Yes, the cells are formatted as dates. If it would simplify things, I could stick a row at the top with the month name. It would eliminate the need for using a wildcard.
Will you always want to total for only one month? You can use the SumIf or SumIfs, but your date range will non-dynamic. If you include a cell to specify the dates you want to sum between, the above formula works well.
If only the one month, you can use some combination of Datevalue and the number days in the month.
ETA: Datevalue("[month name]"&" 1") will return excel date code for that month in the current year. You can use EOMonth formula R determine the excel code for the last day of that month.
This post was edited on 3/5/15 at 2:40 pm
Posted on 3/5/15 at 4:29 pm to LSUtigerME
quote:
Will you always want to total for only one month?
I report monthly on the previous month. My end game is to pull all of this monthly data into a new sheet, so that I can configure lookup to populate the monthly report sheet that I distribute. This is how my daily report currently works(I enter the date, and the appropriate cells pull the data from the matching columns).
This post was edited on 3/5/15 at 4:45 pm
Posted on 3/5/15 at 4:45 pm to h0bnail
Say column A has the date and column B has the info you want to sum.
Insert a column in between A and B. This will be "Month". So now column A is Date, column B is Month, and column C is Quantity.
Populate column B with =MONTH(A1). Drag and drop to fill the range, let's say it goes down 100 rows.
Now over to the right somewhere (say, column G) create a list of months, 1 through 12.
In column H use the following: =SUMIF(B$1:B$100, G1, C$1:C$100). Drag, drop, and have a beer.
Insert a column in between A and B. This will be "Month". So now column A is Date, column B is Month, and column C is Quantity.
Populate column B with =MONTH(A1). Drag and drop to fill the range, let's say it goes down 100 rows.
Now over to the right somewhere (say, column G) create a list of months, 1 through 12.
In column H use the following: =SUMIF(B$1:B$100, G1, C$1:C$100). Drag, drop, and have a beer.
Posted on 3/5/15 at 4:51 pm to h0bnail
quote:
I report monthly on the previous month. My end game is to pull all of this monthly data into a new sheet, so that I can configure lookup to populate the monthly report sheet that I distribute. This is how my daily report currently works(I enter the date, and the appropriate cells pull the data from the matching columns).
In that case, use this formula off to the right instead:
=SUMIF(B1:B100, MONTH(TODAY())-1, C1:C100)
This post was edited on 3/5/15 at 4:56 pm
Posted on 3/6/15 at 8:44 am to h0bnail
Posted on 3/6/15 at 8:47 am to lsu02150
Awesome. I appreciate all of the help. I will update when I get a chance to play with it. It's gotten pushed down my priority list at the moment.
Popular
Back to top
Follow TigerDroppings for LSU Football News