Count value monthly

K

konew1

Well-known Member

Joined

Oct 17, 2007

Messages

2,288

• Nov 5, 2013

• #2

Use =SUMIFS( formula with 2 conditions. One condition >= the first day of the required month and second condition < = the last day of the month.

 

Upvote 0

B

bschwartz

Well-known Member

Joined

Jan 5, 2013

Messages

1,367

• Nov 5, 2013

• #3

Maybe something like this?

Excel 2010
	A	B	C	D	E
1	Date	Sales			May
2	1/3/2013	20521		Sum of Sales	58317
3	1/7/2013	6463
4	2/22/2013	16652
5	3/11/2013	21588
6	3/13/2013	9201
7	3/15/2013	7457
8	3/21/2013	15166
9	5/5/2013	21740
10	5/9/2013	9176
11	5/13/2013	7171
12	5/26/2013	20230
13	6/2/2013	9461
14	6/2/2013	10196
15	6/4/2013	15352
16	6/12/2013	24278
17	6/12/2013	9973
18	6/14/2013	11304
19	7/7/2013	5900
20	7/8/2013	6104
21	7/8/2013	19098
22	7/21/2013	6676
23	8/10/2013	10070
24	8/18/2013	21689
25	8/28/2013	9536
26	9/4/2013	13254
27	9/25/2013	9163
28	9/28/2013	23310
29	9/30/2013	7033
30	10/7/2013	18173
31	10/9/2013	24654
32	10/13/2013	9278
33	10/17/2013	17161
34	10/26/2013	8314
SUMIF

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(--(TEXT(A2:A34,"mmmm")=E1),B2:B34)

 

Upvote 0

S

shyy

Well-known Member

Joined

Nov 6, 2008

Messages

1,484

• Nov 5, 2013

• #4

incase you use dates

=SUMPRODUCT(--($A$2:$A$34>=DATE(2013,5,1)),--($A$2:$A$34<=DATE(2013,5,31)),($B$2:$B$34))

 

Upvote 0

Y

Yvonne Ng

Board Regular

Joined

Nov 5, 2013

Messages

67

• Nov 6, 2013

• #5

Hi,

Allow me to re-phase my question, I've this table shown as follows :

Group </SPAN>	DATE</SPAN>	Amount</SPAN>
Beta</SPAN>	1-Jan-13</SPAN>	$ 4.00 </SPAN>
Beta</SPAN>	1-Feb-13</SPAN>	$ 4.00 </SPAN>
Beta</SPAN>	2-Jan-13</SPAN>	$ 3.00 </SPAN>
Delta </SPAN>	3-May-13</SPAN>	$ 4.00 </SPAN>
Delta </SPAN>	11-Jun-13</SPAN>	$ 5.00 </SPAN>
Beta</SPAN>	1-Jan-13</SPAN>	$ 4.00 </SPAN>
Delta </SPAN>	1-Feb-13</SPAN>	$ 4.00 </SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

I need a formular to shown the total amount in Jan cumulated under Beta group, and similiar to Delta group.

Please assist

 

Upvote 0

H

helpexce

Board Regular

Joined

Oct 30, 2013

Messages

109

• Nov 6, 2013

• #6

Try this.

=SUMPRODUCT(--($A$2:$A$8=E2),--(MONTH($B$2:$B$8)=F2),$C$2:$C$8)
A2:A8 is Group and similar other columns
E2 is either Beta or Delta as your input
In F2 enter 1 to 12 instead of jan to dec. then it will work.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 6, 2013

• #7

Yvonne Ng said:

Hi,

Allow me to re-phase my question, I've this table shown as follows :

Group </SPAN>	DATE</SPAN>	Amount</SPAN>
Beta</SPAN>	1-Jan-13</SPAN>	$ 4.00 </SPAN>
Beta</SPAN>	1-Feb-13</SPAN>	$ 4.00 </SPAN>
Beta</SPAN>	2-Jan-13</SPAN>	$ 3.00 </SPAN>
Delta </SPAN>	3-May-13</SPAN>	$ 4.00 </SPAN>
Delta </SPAN>	11-Jun-13</SPAN>	$ 5.00 </SPAN>
Beta</SPAN>	1-Jan-13</SPAN>	$ 4.00 </SPAN>
Delta </SPAN>	1-Feb-13</SPAN>	$ 4.00 </SPAN>

<TBODY>
</TBODY>

I need a formular to shown the total amount in Jan cumulated under Beta group, and similiar to Delta group.

Please assist

Click to expand...

Let A:C house the table.

E2: Beta
E3: Delta

F1: 1-Jan-13 (perhaps custom formatted as: Jan-13)

F2, copied down:

=SUMPRODUCT($C$2:$C$8,--($A$2:$A$8=$E2),--($B$2:$B$8-DAY($B$2:$B$8)+1=F$1-DAY(F$1)+1))

 

Upvote 0

Y

Yvonne Ng

Board Regular

Joined

Nov 5, 2013

Messages

67

• Nov 6, 2013

• #8

Hi, date require as part of the input, thus the format if date has to be base on dd-mm-yy. And month is customize with character .
Please assist.


Thank you.

 

Upvote 0

Y

Yvonne Ng

Board Regular

Joined

Nov 5, 2013

Messages

67

• Nov 6, 2013

• #9

Hi,
Possible to have month to customize is character ?

please advise .


Thank you

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 6, 2013

• #10

Yvonne Ng said:

Hi,
Possible to have month to customize is character ?

please advise .


Thank you

Click to expand...

You mean to have Jan in F1 (see my post) instead of 1-Jan-13?

 

Upvote 0