- 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.
5
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 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 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/6/15 at 8:44 am to h0bnail
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News